The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I need to create a mix variance where I have 5 Categories. I am totaling up the mix of customers .
Is there a way I can present these calculations in a matrix by category and have the grand total equal the sum of calculation?
Any DAX that can make this work?
My calculations are for each product:
(Current Year Customers - (Current Year Customers * Prior Year Customer Mix %)) / Prior Year Price Per Customer
For Customers who did not make a purchase:
(Current Year Customers who did not make a purchase - (Current Year Customers who did not make a purchase * Prior Year Customers who did not make a purchase Mix %)) / Prior Year Total Price Per Customer who made a purchase
Example:
Current Year Customers:
Product A = 4
Product B = 2
Product C = 45
Product D = 5
Customers with no purchase = 17
Total = 73
Prior Year Customers:
Product A = 6
Product B = 3
Product C = 40
Product D = 4
Customers with no purchase = 15
Total = 68
Prior Year Price Per Customer:
Product A = $10
Product B = $3
Product C = $2
Product D = $8
Total = $2.66
Current Year
Category1 = 2% (Customers who bought product A)
Category2 = 4% (Customers who bought product B)
Category3 = 80% (Customers who bought product C)
Category4 = 4% (Customers who bought product D)
Category5 = 10% (Customers who did not make a purchase)
Prior Year
Category1 = 9% (Customers who bought product A)
Category2 = 4% (Customers who bought product B)
Category3 = 59% (Customers who bought product C)
Category4 = 6% (Customers who bought product D)
Category5 = 22% (Customers who did not make a purchase)
@kdrees21 Are you just having trouble with the totals? If so, then 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
While it is an issue with the grand total row, it is also an issue when trying to calculate the customers without a purchase for Mix % Variance.
since it doesn't have $s associated with it directly it does not populate on the category line, but it does find itself in the grand total.
User | Count |
---|---|
79 | |
74 | |
42 | |
30 | |
28 |
User | Count |
---|---|
108 | |
96 | |
53 | |
48 | |
47 |