Average with ALL/ALLEXCEPT returning wrong value

[SamFletch]

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:

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

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

[v-easonf-msft]

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

[v-easonf-msft]

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

[Anonymous]

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.