Nested Sum and Average

[deepblue_m45]

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 · Accepted solution]

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]

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!