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] /
CALCULATE(
SUM(
'Base'[Sales Vol]
);
FILTER(
ALL('Base');
'Base'[Period]=EARLIER('Base'[Period])
)
)
I hope i'm being clear enough to get an answer. thanks in advance!
regards
