Average Total for a Measure

[Anonymous]

Hello - for the measure 'diff_v1_v2', I want to see the difference between Value 1 & 2 when there's context, but for the totals line, I want to see the average of the difference for the rows with context. See the Excel snapshot for the result I am looking for.

DAX, incorrect	Excel, correct

diff_v1_v2 = IF( ISINSCOPE('Date Table'[FYFW]),

sum('Table1'[Value2]) - sum('Table1'[Value1]),

AVERAGEX('Table1', sum('Table1'[Value2]) - sum('Table1'[Value1])))

[DataInsights]

@Anonymous,

You need to wrap the second argument of AVERAGEX in a CALCULATE function in order to use context transition. Alternatively, you can use base measures as demonstrated below (simpler and more flexible). Notice that I changed the first argument of AVERAGEX to 'Date Table'. It's better to iterate dimension tables rather than fact tables (performance). In the visual, I used 'Date Table'[FYFW].

Value 1 = SUM ( Table1[Value1] )

Value 2 = SUM ( Table1[Value2] )

diff_v1_v2 = 
IF (
    ISINSCOPE ( 'Date Table'[FYFW] ),
    [Value 2] - [Value 1],
    AVERAGEX ( 'Date Table', [Value 2] - [Value 1] )
)

[Anonymous]

@DataInsights ,

Thank you! Simple enough... I've revealed an issue though. I used your code - making Value1 & Value2 their own measures to use in the difference calculation. Results below. I think mine isn't matching yours because my date table goes down to the day, not aggregated by week. Is there a way to have the AVERAGEX calculate based on whatever level I am showing? Example: if I did have it showing by date, the -115,188 would be correct, but if I am at the weekly level, it should show what you have (-806,315), and so on by month, quarter etc.

Value 1 = SUM ( Table1[Value1] )
Value 2 = SUM ( Table1[Value2] )
diff_v1_v2 = 
IF (
    ISINSCOPE ( 'Date Table'[FYFW] ),
    [Value 2] - [Value 1],
    AVERAGEX ( 'Date Table', [Value 2] - [Value 1] )
)

[DataInsights · Accepted solution]

@Anonymous,

You can create a SWITCH statement that detects which level of Date Table is being used. You'll have to code each scenario in the SWITCH statement. This is an example of how you can modify AVERAGEX:

AVERAGEX ( VALUES ( 'Date Table'[FYFW] ), [Value 2] - [Value 1] )

The article below describes the differences between ISINSCOPE and HASONEVALUE. Be sure to understand the differences so you can use the appropriate function.

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/ 

[Anonymous]

GOT IT @DataInsights ! Thank you for the article concerning ISINSCOPE vs HASONEVALUE, that is something new for me. I did one test with the SWITCH function and this will get me what I need. Much obliged!

[DataInsights]

Excellent!