March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
hi All,
I wanted to seek help in calculating % Contribution of sales of company from each segment - the data is as below:
Company | Segment | Share | Sales |
A | X1 | 20% | 100 |
A | X2 | 30% | 200 |
B | X1 | 10% | 100 |
B | X2 | 5% | 300 |
C | X1 | 20% | 500 |
I am using a slicer in Segment column. What I am looking to do is the following:
Please suggest a solution.
Regards,
Kaushik
See if this is what you want. Not 100% sure I followed your requirements exactly. This is my result, and as far as I can tell, the slicer operates as you desired:
These are the 3 measures I used:
% Sales Contribution =
VAR Numerator = SUM(Sales[Sales])
VAR Denominator =
CALCULATE(
SUM(Sales[Sales]),
ALLSELECTED(Sales[Segment])
)
RETURN
DIVIDE(Numerator,Denominator,0)
Weighted Share = SUM(Sales[Share]) * [% Sales Contribution]
Weighted GM Share =
CALCULATE(
[Weighted Share],
ALLEXCEPT(Sales,Sales[Company])
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks for the quick response and this works well.
CALCULATE(PRODUCTX(Sheet1,[Weighted Share]),ALLEXCEPT(Sheet1,Sheet1[Company]))<div> </div>
In simple math what are you trying to multiply? Product is X * Y. I thought that when I did the weighted share, which you said was correct, that is the product of sales share * % sales contribution.
But what do you want Weighted GM Share to be the product of? It cannot just be "product of each companys weighted Share." It needs to be "product of each companys weighted Share and something else."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI should have been more clear. Basically we need to product each company's Weighted Share by market segment. For instance, for Company A, it should be 6.67% * 20%, while for company B it would be 2.5% * 3.75%.
Regards,
Kaushik
Just an FYI @Anonymous I have not forgotten this, but I spent nearly a hour on it last week and couldn't get it to do what I wanted. I have to spend more time working through the logic of the tables Power BI is creating in memory. Someone else might jump in, but I'm a little stumped right now and need to find time to work on it some more. Love a challenge!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
89 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |