Removing Context From SUMMARIZE

DAX DAX Calculations
1

Hi everyone - first time, long time…

I have created a measure to calculate the Absolute Variance at different aggregated levels of a hierarchy. This is working as expected and also works for YTD if multiple months are selected in a slicer.

Absolute Variance = 
VAR RowAbs = AVERAGEX(VALUES(Codes[Category]),ABS([Actual vs Selected Version Var]))
VAR TotalAbs = SUMMARIZE(Expenses,Codes[Category],'Business Units'[BU Name],"Sum Category",[Actual vs Selected Version Var])

RETURN
IF( HASONEVALUE( Codes[Category] ) ,
    RowAbs,
        SUMX(TotalAbs,ABS([Sum Category])))

I am having trouble creating a measure to calculate the YTD results with only one month selected in a slicer. I (think I) understand what I need to do:

  • Add a date column to the TotalAbs variable
  • remove the date context from the TotalAbs variable
  • filter to be <= the select month

I have attempted to use ALL and CALCULATETABLE without success.

Here is a file for reference Costs.pbix (153.7 KB)

Many thanks!

Jeremy

2

Jeremy,

Please clarify what you are wanting solved here. Are you looking for the result that shows up in the Cost By Month Working As Expected to be the same in the Cost YTD Not Working Tab? Please post the correct result you are looking for.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

Hi

The “Cost By Month Working As Expected” numbers are correct.

On the “Cost YTD Not Working” page the “Abs Var YTD” is not correct for Winery, Winery 1, Winery 2, Winery 3 and Total (i.e., the VAR TotalAbs part of the formula is wrong - it’s calculating only for the month, not the YTD month).

Hope this clarifies things.

Jeremy

4

Jeremy,

Is this the result you were looking for?

image
image1310×397 21.6 KB

Thanks
Enterprise%20DNA%20Expert%20-%20Small

5

Thanks - exactly what I was looking for.

1 Like