Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kdrees21
Helper I
Helper I

Help with Mix Variance

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)

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.