Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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!
Solved! Go to 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] )
)
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] )
)
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!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
12 | |
11 | |
10 | |
9 |