Forum Discussion
Calculate share contribution
Hi everyone,
I'm trying to solve one problem I have, but with no success.
I'm havig matrix visual with this simplified structure (total is not part of the fact table, it's automatically calculated in matrix):
| Category group | Value |
| G1 | 10 |
| G2 | 5 |
| Total | 15 |
In the fact table there is a Brand column that is placed in a dropdown, so a user can select subset of brands and see it's share and other metrics.
Current calculation I'm using to calculate market share:
Value = SUM('Fact Data'[Value])
Value_all = CALCULATE(SUM('Fact Data'[Value]),REMOVEFILTERS('Fact Data'[Brand]))
Share = DIVIDE(Value, Value_all)
But in this setting it would display market shares by group (for example):
| Category group | Value | Value_all | Share |
| G1 | 10 | 20 | 0.5 |
| G2 | 5 | 30 | 0.167 |
| Total | 15 | 50 | 0.3 |
What I'd also need is a so called market contribution - so to calculate contribution of each group to total:
| Category group | Value | Value_all | Share | Contribution |
| G1 | 10 | 20 | 0.5 | 10/50=0.2 |
| G2 | 5 | 30 | 0.167 | 5/50=0.1 |
| Total | 15 | 50 | 0.3 | 0.3 |
Is it possible to do such a thing? Just another notice, this example is simplified version, there are other dropdowns like country, time period, category, subcategory etc, so I'd need some kind of dax expression that would calculate sum on entire fact data table under current context and ignore groups (rows in matrix)
2 Replies
Hi alexpantex ,
Based on the sample and description you provided, Please try code as below to create two measures.
Total_Value = CALCULATE(SUM('Fact Data'[Value]), ALL('Fact Data'))Contribution = DIVIDE([Value_Category],[Total_Value])Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yulia,
Thanks for the reply.
This approach doesn't help since as I mentioned there are other dropdowns like country, time period, category, subcategory that are used to filter data, so using ALL() would evaluate it on entire dataset, instead on that subset.
