Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to calculate Market Share without taking into account mix effect between Key Accounts and i'm stuck. i can do the calculations in Excel but not in DAX.
i've got a table similar to this one (with more columns: products, size, etc):
Period | Key Account | Manufacturer | Sales Vol |
January | KA 1 | A | 100 |
January | KA 1 | B | 50 |
January | KA 2 | A | 50 |
January | KA 2 | B | 50 |
January | KA 3 | A | 50 |
January | KA 3 | B | 50 |
February | KA 1 | A | 110 |
February | KA 1 | B | 70 |
February | KA 2 | A | 55 |
February | KA 2 | B | 50 |
February | KA 3 | A | 55 |
February | KA 3 | B | 50 |
So, Share taking into account sales mix between channels would be:
Manuf | January Share | February Share | Var W/Mix |
A | 57% | 56% | -0,7% |
B | 43% | 44% | 0,7% |
share A in January = A total vol in January / total vol in January
But i want to get market share variation for February without taking into account intra KA effect for Manufacturer A:
Share A | January | February | Variation W/MIX | Weight KA Feb | Var WO/Mix | |
KA 1 | 67% | 61% | -6% | 46% | -2,6% | |
KA 2 | 50% | 52% | 2% | 27% | 0,6% | |
KA 3 | 50% | 52% | 2% | 27% | 0,6% | |
0,5% | Intra KA mix | |||||
-0,7% |
In the example, as KA 1 increased their total sales volume participation in February and as Manuf A Share in that KA it's above average Share, the loss of market share in that KA would represent -2,6%.
-6%=January Share - February Share
-2,6%= (january share - february share) * weight KA Feb -> this is the formula i need
This is the formula i'm using for market share with mix:
Share = 'Base'[Sales Vol] /
help!!
help please 😞
Hi @Anonymous ,
I can't understand how did you get the following result:
The sum of data 67%, 50%, 50% is more than 100%
Best Regards,
Teige
@TeigeGao it's only the share of Manufacturer A in those KA. to get 100% for each KA we have to add manuf B.
for getting 100% i should do same calculations as column weight:
Weight | ||||
Share A | January | February | January | February |
KA 1 | 28,6% | 28,2% | 50,0% | 50,0% |
KA 2 | 14,3% | 14,1% | 25,0% | 25,0% |
KA 3 | 14,3% | 14,1% | 25,0% | 25,0% |
57% | 56% | 100% | 100% |
i don't know if i can share PBIX or XLS files over here to show you where i'm stuck, it's simpler than copying the tables and not being able to show the formulas.
Let me konw if it's clearer now my problem. thanks for your time!
I would really appreciate help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |