Search code examples
daxpowerbi-desktopdaxstudio

Powerbi Producing wrong totals (Alternative to MAX )

I have two dax code below working perfectly,

Current Week Amt = 
VAR CurrentFiscalWeek = MAX( 'DF'[Fiscal Week num] )
RETURN
CALCULATE(
     [Measure],
    'DF'[Fiscal Week num] = CurrentFiscalWeek
)

Current Week Amt_2 = 
SUMX(
    SUMMARIZE('DF', 'DF'[Source], 
              "fixed" ,[Current Week Amt]),
              
              [fixed])

yet the first one is not giving the right totals. Because Fiscal Week num differs. I change the Current Week Amt_2 for each table that I have. but i was wondering if there is a way to leverage VAR CurrentFiscalWeek = MAX( 'Inventory'[Fiscal Week num] ) so the table total may come correctly ? many thanks in advance.

 Source    Fiscal Week num
    Company A       52
    Company B       49
    Company C       48

enter image description here

Solution

  • For totals in visuals, these are not totalled at the visual level (ie what you see is what gets added). Instead, these are calculated with the same DAX formula. With that in mind, the MAX( 'DF'[Fiscal Week num] ) is catching you out.

    Try the following which takes all the available [Fiscal Week num]:

    Current Week Amt = 
  VAR fiscalWeeks = DISTINCT( 'DF'[Fiscal Week num] )
  RETURN
    CALCULATE(
      [Measure],
      'DF'[Fiscal Week num] IN fiscalWeeks
   )

    Note the use of DISTINCT (you could also use VALUES) and the IN operator.

    Follow-up - try this version for when each dimension will have a different latest [Fiscal Week num].

    Current Week Amt = 
  SUMX(
    DISTINCT('DF'[Source]),
    (
      VAR CurrentFiscalWeek = MAX( 'DF'[Fiscal Week num] )
      RETURN
        CALCULATE(
          [Measure],
          'DF'[Fiscal Week num] = CurrentFiscalWeek
        )
    )
  )

    SUMX iterates through every row of the table for the calculation, and in this case it is for every 'DF'[Source]. At the row level in your visual it will do it for just the one Source, and for the total, it will do it for all of them.