Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average Total for a Measure

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, incorrectExcel, correct

 

 

 

diff_v1_v2 = IF( ISINSCOPE('Date Table'[FYFW]),
sum('Table1'[Value2]) - sum('Table1'[Value1]),
AVERAGEX('Table1', sum('Table1'[Value2]) - sum('Table1'[Value1])))
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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/ 

5 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        DataInsights
        Super User
        4 years ago

        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/