Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
26 | |
20 | |
13 | |
8 |
User | Count |
---|---|
76 | |
48 | |
46 | |
20 | |
16 |