In Matrix, Measure is rows are Summed, but Totals need to be averaged

[wyattexplore]

Hey PBI,

I couldn't seem to find a concrete answer on this, so I apologize if this is a duplicate thread. 

I have a measure to calculate employee utilization on my matrix as follows.

Utilization = 'Employee Hours Table'[Total Billable Hours] / CALCULATE('Date Table'[Total Expected Hours])

Total Expected Hours =
CALCULATE(
        SUM( 'Date Table'[Workday Hours]), ALLSELECTED( 'Date Table'[Date]))

Total Billable Hours =

CALCULATE(
        SUM( 'Hours Table'[Amount]), [Hours Type]="Billable")

I hope that makes sense. [Workday Hours] is a calculated column that determines if the day is Mon-Fri and not a holiday and then uses "8" for the expected hours. The ALLSELECTED it uses the full expected hours for a date slicer, and not just for the days that the user had input time.

The issue I am running into is on my matrix, the rows are displaying correctly, however the Total is displaying as a sum of percentage, when I need it to display as an average of percentages. I can't seem to change it as this is just a measure. Would anyone have any advice on how to fix this?

[v-lili6-msft]

hi, @wyattexplore 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[wyattexplore]

Unfortunately I did not find this solution to work yet. I am still trying to figure out why it isn't working correctly. @v-lili6-msft 

[v-lili6-msft]

hi, @wyattexplore 

This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

You could use AVERAGEX to get it.

If you still have the problem, please share a sample pbix file.

Best Regards,

Lin