Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Percentage Sales dependent on Slicer

hi All,

 

I wanted to seek help in calculating % Contribution of sales of company from each segment - the data is as below:

CompanySegmentShareSales
AX120%100
AX230%200
BX110%100
BX25%300
CX120%500

 

I am using a slicer in Segment column. What I am looking to do is the following:

  1. % Sales contribution: Calculate % sales contribution from each row based on segment's selected 
    • For instance, Company A with X1 Selected in slicer, answer should be 100%, while Company A with X1 and X2 selected in slicer gives 33% and 67% respectively 
  2. Weighted Share: Next step is to have 'Share' ^ '% Sales contribution' calculated in step 1
  3. Weighted GM Share: Calculate a Product of 'Weighted Share' for each company to give each company one final score

Please suggest a solution.

 

Regards,

Kaushik

 

5 REPLIES 5
edhans
Super User
Super User

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:

20200214 15_00_54-Untitled - Power BI Desktop.png

 

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])
)


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

thanks for the quick response and this works well.

 
Just the last measure, I need a product of each companys weighted Share. I used the following formula, however it gives me an incorrect result. I tried a simple PRODUCT instead of PRODUCTX, however that doesnt give me an option of performing calculation on Weighted Share measure. 
 
can you suggest which part of the query is incorrect or share the formula which could work for me?
 

 

 

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."

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I 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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.