Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
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
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.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |