Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ninos-shiba
Resolver I
Resolver I

How can I find the average of this measure that is using SWITCH to include the 0 in the calculation?

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:

ninos-shiba_0-1600098782711.png

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:

ninos-shiba_1-1600098982055.png

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?

 

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @ninos-shiba ,

 

Please refer to my .pbix file.

My fact table may be different from yours, but the calculation formula should be similar.

v-lionel-msft_0-1600311429618.png

 

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.

 

View solution in original post

12 REPLIES 12

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.