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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Issues with having a right formula

I am trying to get the right subtotals and total averages in the utilisation matrix report I am creating. I am using this DAX: IFERROR(IF(ISINSCOPE('X'[Y]),[sum]/[count],[Utilisation]),BLANK())
 
I have different type of utilisation, and for some reason the results that it provide in both are the same and I am not sure why the "sum" formula does not provide the right infromation I need.
 
sum = SUMX(Table, Utilisation)
#Thank you
5 REPLIES 5
Anonymous
Not applicable

So I have Practice on top, then group and the resource, divided by months. But my matrix has some blanks on the resources, so I want that the subtotals for Group and Practice has the right average of utilisation ignoring the blanks.

 

Something like this:

 

alexacg_0-1685449421088.png

 

Is that something you have reviewed in the past?

@Anonymous 

Please try

using Utilisation_Product =
AVERAGEX ( VALUES ( 'X'[Y] ), COALESCE ( [Utilisation], 0 ) )
tamerj1
Super User
Super User

Hi @Anonymous 

if Utilisation is a measure tye

sum = SUMX ( VALUES ( 'X'[Y] ), [Utilisation] )

Anonymous
Not applicable

Hi Tamerj1, thank you so much for your help on this :). That definitely solved my sum formula, but it did not fix my error on the matrix to average correctly the product and subproduct categories I have ignoring the blanks, and I was using Utilisation_Product= IFERROR(IF(ISINSCOPE('X'[Y]),[sum]/[count],[Utilisation]),BLANK()), any idea about this?

@Anonymous 

How does your matrix look like? What columns are placed in the matrix? What results are you expecting?

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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