Forum Discussion

kdrees21's avatar
kdrees21
Helper I
5 years ago

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