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 Community!
Overview: I am trying to show the average count of activities completed by person by hour for an entire team versus a selected individual on the same plot using a slicer.
My data looks something like this. Each activity has a unique id and I want a count for each individual in each hour and then the average per person per hour.
The individual count is pretty straight forward - just a distinct count of id. I then created a measure to calculate the average per person per hour which works fine when all the team members are selected but it changes to only show one team member when one team member is chosen. Can someone help me figure this out? I feel like either my measure is wrong or I need some fancy data model that I'm just not seeing.
Here is the measure for average count per person per day that I'm using. I added the ALL() to try to ignore the slicer for this measure but that is not working (is the ALL() in the wrong place?).
Avg by Person by Hour = AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Owner],
'Table'[Actual End by Hour],
"Hourly Count", CALCULATE(DISTINCTCOUNT('Table'[id]), ALL('Table'[Owner]))
),
[Hourly Count]
)
When all people are selected, I get the count I want:
(Note, these plots are showing a single day)
But when I select just one person the average measure changes to just be that one person.
Any help is very much appreciated!
Solved! Go to Solution.
Hi @amhiggins ,
Try the following code:
Avg by Person by Hour =
AVERAGEX (
CALCULATETABLE (
SUMMARIZE (
'Table',
'Table'[Actual End By hour],
'Table'[Owner],
"@IDCount", DISTINCTCOUNT ( 'Table'[ID] )
),
ALL ( 'Table'[Owner] )
),
[@IDCount]
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @amhiggins ,
You need to use the all in the summarize not in the CALCULATE. Measure are based on context and you want to calculate the values for each of the persos within the selection, what you are doing is first select the filtered values by person and the calculate the values for all the selection.
You need to do the contrary so first select all the data and then do the calculation:
Avg by Person by Hour = AVERAGEX(
SUMMARIZE(
ALL('Table'),
'Table'[Owner],
'Table'[Actual End by Hour],
"Hourly Count", DISTINCTCOUNT('Table'[id])
),
[Hourly Count]
)
Be aware that you may need to use ALLSELECTED instead of all because of the transiction context:
Avg by Person by Hour = AVERAGEX(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Owner],
'Table'[Actual End by Hour],
"Hourly Count", DISTINCTCOUNT('Table'[id])
),
[Hourly Count]
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for the response! When I move the ALL up in the summarize I lose the granularity of the calculation by hour - it just gives me a single average calculation that is the same for every hour - is there any way to keep the hour to hour calculation?
I also have some other filters that I'd like to preserve on the data (such as the date) so I tried using the command below but doing this causes the value to change when I use the Person slicer - am I using ALLEXCEPT incorrectly?
Avg by Person by Hour = AVERAGEX(
SUMMARIZE(
ALLEXCEPT('Table', [OTHER-FILTERED-COLUMNS...]),
'Table'[Owner],
'Table'[Actual End by Hour],
"Hourly Count", DISTINCTCOUNT('Table'[id])
),
[Hourly Count]
)
Hi @amhiggins ,
Try the following code:
Avg by Person by Hour =
AVERAGEX (
CALCULATETABLE (
SUMMARIZE (
'Table',
'Table'[Actual End By hour],
'Table'[Owner],
"@IDCount", DISTINCTCOUNT ( 'Table'[ID] )
),
ALL ( 'Table'[Owner] )
),
[@IDCount]
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSadly, that calculation changes when I select a person in the slicer. 😞
Hi @amhiggins ,
Can you share a sample file? I made a test file and work properly.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI went to create the sample file and found that one of my other filters on the page was causing the problem with your last suggestion - It looks like the last formula will do exactly what I want!!
Thank you so much for your help!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |