The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Basically, I want to build a graph that shows the % of employees in our business unit that have received 2 coaching sessions per month. I created a measure:
Filter Measure = SWITCH(TRUE(), COUNT(Merge1[Master Roster.Rep ID]) >= 2, 1, COUNT(Merge1[Master Roster.Rep ID]) = 1, .50, COUNT(Merge1[Master Roster.Rep ID]) < 1, 0)
Which looks correct on this table:
In July, filtering for those 3 employee ID numbers, U34826 did receive 2 coaching sessions, so he/she met the goal for that month (100%). Employee U35034 did not receive any coaching for that month, so he/she is 0%. Employee U37046 only received 1 coaching that month, so he/she is 50%.
Where I'm stuck now is, I want to show the average of these employees percentages for each month but can't figure out how to do so since the average calculation:
Measure2 = AVERAGEX(VALUES(Merge1[Master Roster.Rep ID]), [Filter Measure])
is returning these values:
And calculating the average total for July 2020 as 75%, but it should actually be 50% since the employee who did not receive any coaching needs to be factored into the calculation.
The correct logic I'm hoping for would be (for July 2020):
(100% + 0% + 50%) / 3 distinct employees = 50%, not 75%
It's only taking 2 distinct employees and ignoring the employee with a 0%. How can I solve this?
Solved! Go to Solution.
Hi @ninos-shiba ,
Please refer to my .pbix file.
My fact table may be different from yours, but the calculation formula should be similar.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ninos-shiba ,
Please refer to my .pbix file.
My fact table may be different from yours, but the calculation formula should be similar.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@ninos-shiba This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
You could use a FILTER to either wrap your SUMMARIZE or inside your SUMMARIZE to FILTER "Table".
@Greg_Deckler , how would I filter it?
AvgCoaching = AVERAGEX ( SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ), [Measure])
What would replace [Measure]?
@ninos-shiba Try:
AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),NOT(ISBLANK([Measure]))), [Measure])
@ninos-shiba Didn't realize it returned 0 and not BLANK, try:
AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),[Measure]=0), [Measure])
@Greg_Deckler , this formula is returning empty now:
AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),[Measure]=0), [Measure])
@ninos-shiba Shoot, logic was reversed:
AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),[Measure]<>0), [Measure])
@Greg_Deckler , it went back to showing 100%:
Do I need to factor in the Rep ID into the calculation, perhaps?
@ninos-shiba What are you expecting for output. What I need is sample data and expected output from that sample data, then I can put together the correct calculation. I also would like to see how that matrix is configured.
@Greg_Deckler , I'm expecting that it calculates the average based off the values we see in the Filter Measure column in the matrix like this: (100% + 50% + 0% / 3 distinct employees. It's not factoring in the employee that had a 0% so the calculation is (100% + 50%) / 2.
Here is what the properties of my matrix visual are:
I can't figure out how to add sample data. Give me a moment.
The forums won't allow me to copy and paste. Here is a screenshot at least.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |