Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Average with ALL/ALLEXCEPT returning wrong value

Hello all,


I'm trying to calculate an average value with some ALL and ALLEXCEPT filters but the value being returned is incorrect and I don't know why.


I have data with suppliers and job types. I want to take the average cost for the job type, regardless of supplier and compare that to the average cost of the job type across the network.


To do that, I've wrote the following measure:



THINK this is right but I'll be honest and admit that I'm terrible with the ALL and ALLEXCEPT forumlas. 


The reulst in the table is half right:



Whilst the Average Job Type Case figure is right (I think) the total number is different, which does not make sense to me. I am then calculating the percentage difference between the Total Spend and Average Job Type Cost and whilst this is returning correctly in the table, again, once it totals up, it's taking the total number.


When I then look to return this to a measure on its own, it's taking the total (and therefore) incorrect figure:



I'm 99% certain this is me amking a mistake with the ALLEXCEPT forumla but no matter what changes I try (switiching it to ALL and changing the average function) I can't seem to fix it. 


COULD create calaculated colums and return the selected value but that's only going to slow my report down in the long run and it seems clumsy. 


Can someone please advise where I may be going wrong?


Thank you.

Community Support
Community Support

Hi, @SamFletch 

Could you please tell me whether your problem has been solved?

For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason


Community Support
Community Support

Hi, @SamFletch 
I create a sample file,but didn't find anything  wrong.

I guess that you may missing some filter conditions in measure Average Job Type Cost . If possible , can you share your sample data/pbix file for testing?



How will the table visual display if you only apply the field "Job type" and "Average Job Type Cost " to the table "Values" ?


Best Regards,
Community Support Team _ Eason


Not applicable

Hi @SamFletch 


Do one thing use this Dax to calculate the average = averagex(allselected(supplier),total cost). This will give you the result.


Kindly mark this solution as accepted.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors