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

[ninos-shiba]

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?

[v-lionel-msft · Accepted 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.

[Greg_Deckler]

@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".

[ninos-shiba]

@Greg_Deckler , how would I filter it?

AvgCoaching = AVERAGEX ( SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ), [Measure])

What would replace [Measure]? 

[Greg_Deckler]

@ninos-shiba Try:

AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),NOT(ISBLANK([Measure]))), [Measure])

[ninos-shiba]

That did not work @Greg_Deckler :

[Greg_Deckler]

@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])

[ninos-shiba]

@Greg_Deckler , this formula is returning empty now:

AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),[Measure]=0), [Measure])

[Greg_Deckler]

@ninos-shiba Shoot, logic was reversed:

AvgCoaching = AVERAGEX ( FILTER(SUMMARIZE ( Merge1, 'Date Table'[Month 2] , "Measure", [Filter Measure] ),[Measure]<>0), [Measure])

[ninos-shiba]

@Greg_Deckler , it went back to showing 100%:

Do I need to factor in the Rep ID into the calculation, perhaps?

[Greg_Deckler]

@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.

[ninos-shiba]

@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.

[ninos-shiba]

The forums won't allow me to copy and paste. Here is a screenshot at least.