Forum Discussion

deepblue_m45's avatar
deepblue_m45
Frequent Visitor
4 years ago
Solved

Nested Sum and Average

Hello -    I have a table that I'm using, coming from a SQL Server as a direct query, so my options to transform are limited. I'm wondering if it's possible to achieve the below flow through a meas...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Expected avg measure: =
VAR _runtimetable =
    FILTER (
        ADDCOLUMNS (
            VALUES ( VW_Attendance[Run time] ),
            "@countsum", CALCULATE ( SUM ( VW_Attendance[Count] ) )
        ),
        [@countsum] <> 0
    )
RETURN
    IF (
        HASONEVALUE ( VW_Attendance[Section] ),
        AVERAGEX ( _runtimetable, [@countsum] )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

Expected avg measure: =
VAR _runtimetable =
    FILTER (
        ADDCOLUMNS (
            VALUES ( VW_Attendance[Run time] ),
            "@countsum", CALCULATE ( SUM ( VW_Attendance[Count] ) )
        ),
        [@countsum] <> 0
    )
RETURN
    IF (
        HASONEVALUE ( VW_Attendance[Section] ),
        AVERAGEX ( _runtimetable, [@countsum] )
    )
deepblue_m45's avatar
deepblue_m45
Frequent Visitor
4 years ago

Wow this is perfect, thank you!

Would there be an easy way to enable the total to sum them? I looked at yours, and mine, and the total is blank. I thought it was a setting but it looks like they are all enabled. Thanks again!