DAX -Average Calculation for a category not matching up to sub-category level

[Anonymous]

I have a dataset on revenue and volume of certain SKUs, rolled-up into product categories, and then into customers. I am trying to calculate average selling price (formula:revenue by volume) for each SKU, and subsequently for each category, and eventually for each customer. 

The issue is that ASP at the SKU level is calculated correctly, however the numbers don't make sense at category, and customer level. For example, in 2017, it says ASP for category 1 is 0.90, when in fact, category 1 is made up of many SKU with majority of them havnig 20+ ASP.

Both the sample dataset and the PBI report can be accessed through here: Data  

Thanks in advance!

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, just add a new measure as below:

Measure 3 = 
var _table=SUMMARIZE(Sheet1,Sheet1[Customer Name],Sheet1[Product Category],Sheet1[Product],Sheet1[Year],"_value",[ASP])  return
AVERAGEX(_table,[_value])

Result:

Regards,

Lin

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my 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

[Anonymous]

Hi Greg - Thanks for the quick reply. Unfortunately, my issue is different to the solution you were referencing to. 

The issue is not with the incorrect 'Total' row that falls at the bottom of a table/matrix, but rather a case of an aggregation of a measure, which is then further aggregated. In my case, average selling price by each SKU, which then aggregates to average selling price by product category, and subsequently by customer.