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 measure. 

Essentially I have a large data set that records guest counts every 6 hours ('RunTime') by floor ('Area') and by areas of the facility ('Section'). I want to get an hourly total of all floors by section and then average it.

So for the green emergent section, I would sum floors 1 through 5 at the 1:00 hour to get a total of 48. Then doing the same for 6, 12, and 24 hour to get 48, 48, 61, 36 respectively. Then I want to average it to get a 'daily average', which is about 48.25.

Then to add some MORE complexity 😁 there's one section that does have zero's in it so I would like to add a <>0 to the average portion (as seen highlighted in yellow). 


Thanks as always for the help!


 

 

  • 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] )
    )

2 Replies

  • 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!