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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help in calculating % contribution of Product

Hi Team,

 

Greetings!

 

We have the data like below in the table

"Product""Brand""Sales of a product""Total Sales of Brand""% contribution"
qHvuxw462862449.5471814216.898.10%
zzbyvmy317818893.5348501915.191.20%
pwgxvux337370658.1434917003.977.60%
eSymguyy552194562.3849615786.665.00%
cFuzxxpzuxny90712588.364737647314.00%
cHypzuxns (Guwvp)90563575.8649389995.913.90%
czuxxpuz77627165.864737647312.00%
cMwnw ymbwuyx64144744.46473764739.90%
cCvpynwx56665595.3594481668.29.50%
yxcznduz15401786518742644408.20%
yGzuvvs/Gzuvvmyp146135531.318742644407.80%
xx108636862.438259885292.80%
xuxpxdxu/uxpznphyu59963929.138259885291.60%
xChwuxb56673293.138259885291.50%
xwmzcwu49414747.638259885291.30%
xuwzwu46715174.638259885291.20%
dwpHyu Buznd Mzukyps444263957.9 0
mwpHyu Buznd Mzukyps384095375 0
uwpHyu Buznd Mzukyps291101111 0

 

"sales of a product" and "Total sales of Brand" are measures . 
we wanted to calculate "% contribution" of the product .

 

the logic : sales of the product / if (product = brand) then sales of that one brand ,else max (sales of brand)

 

one product might have many brands, so we need to take the max of the brand sales

 

equivalent tableau logic

 

% Contribution (copy) =
SUM([product sales])/MAX(
IF {FIXED [Product]: MAX([Product] = [brand])} THEN
IF [Product] = [brand]
THEN [Total Sales of Brand] END
ELSE [Total Sales of Brand] END)


Total Sales of Brand= { FIXED [Brand]:SUM([brand sales])}

 

Please help to convert the same in power BI

@Greg_Deckler @amitchandak 

expected output

"Product""Brand""Sales""Total Sales of Brand""% contribution"
cHypzuxns (Guwvp)90563575.8649389995.913.90%
dwpHyu Buznd Mzukyps444263957.9 0
eSymguyy552194562.3849615786.665.00%
mwpHyu Buznd Mzukyps384095375 0
pwgxvux337370658.1434917003.977.60%
qHvuxw462862449.5471814216.898.10%
uwpHyu Buznd Mzukyps291101111 0
xx108636862.438259885292.80%
yxcznduz15401786518742644408.20%
zzbyvmy317818893.5348501915.191.20%

 

Thanks,

Ananth 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you base on provided data, please check whether that is what you want.

1. Create a measure as below to get the sales

Sales = 
VAR _selproduct =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Brand] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selproduct )
    )
VAR _samepb =
    CALCULATE (
        MAX ( 'Table'[Brand] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _selproduct
                && 'Table'[Brand] = _selproduct
        )
    )
VAR _maxsales =
    CALCULATE (
        MAX ( 'Table'[Sales of a product] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selproduct )
    )
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@rank",
            RANKX (
                ALLEXCEPT ( 'Table', 'Table'[Product] ),
                CALCULATE ( MAX ( 'Table'[Sales of a product] ) ),
                ,
                DESC
            )
    )
RETURN
    IF (
        _count = 1,
        SUM ( 'Table'[Sales of a product] ),
        IF (
            _count > 1,
            IF (
                _samepb = _selproduct,
                CALCULATE (
                    SUM ( 'Table'[Sales of a product] ),
                    FILTER ( 'Table', 'Table'[Brand] = _samepb )
                ),
                MAXX ( FILTER ( _tab, [@rank] = 1 ), [Sales of a product] )
            )
        )
    )

2. Create a measure as below to get the Total Sales of Brand

NTotal Sales of Brand = IF ( ISBLANK ( [Sales] ), BLANK (), SUM ( 'Table'[Total Sales of Brand] ) )

3. Create a measure as below to get the % contribution

% contribution = 
IF (
    ISBLANK ( [Sales] ),
    BLANK (),
    DIVIDE ( [Sales], [NTotal Sales of Brand], 0 )
)

yingyinr_0-1647595373977.png

Best Regards

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

HI @Anonymous ,

Thank you very much for checking the issue . 

Let me replicate the solution from my end and get back to you.

 

Thanks,

Ananth

Anonymous
Not applicable

Hi @Anonymous ,

OK. I'm looking forward to your feedback. 😀

Best Regards

Anonymous
Not applicable

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you base on provided data, please check whether that is what you want.

1. Create a measure as below to get the sales

Sales = 
VAR _selproduct =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Brand] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selproduct )
    )
VAR _samepb =
    CALCULATE (
        MAX ( 'Table'[Brand] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _selproduct
                && 'Table'[Brand] = _selproduct
        )
    )
VAR _maxsales =
    CALCULATE (
        MAX ( 'Table'[Sales of a product] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selproduct )
    )
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@rank",
            RANKX (
                ALLEXCEPT ( 'Table', 'Table'[Product] ),
                CALCULATE ( MAX ( 'Table'[Sales of a product] ) ),
                ,
                DESC
            )
    )
RETURN
    IF (
        _count = 1,
        SUM ( 'Table'[Sales of a product] ),
        IF (
            _count > 1,
            IF (
                _samepb = _selproduct,
                CALCULATE (
                    SUM ( 'Table'[Sales of a product] ),
                    FILTER ( 'Table', 'Table'[Brand] = _samepb )
                ),
                MAXX ( FILTER ( _tab, [@rank] = 1 ), [Sales of a product] )
            )
        )
    )

2. Create a measure as below to get the Total Sales of Brand

NTotal Sales of Brand = IF ( ISBLANK ( [Sales] ), BLANK (), SUM ( 'Table'[Total Sales of Brand] ) )

3. Create a measure as below to get the % contribution

% contribution = 
IF (
    ISBLANK ( [Sales] ),
    BLANK (),
    DIVIDE ( [Sales], [NTotal Sales of Brand], 0 )
)

yingyinr_0-1647595373977.png

Best Regards

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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