Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Team,
Greetings!
We have the data like below in the table
"Product" | "Brand" | "Sales of a product" | "Total Sales of Brand" | "% contribution" |
q | Hvuxw | 462862449.5 | 471814216.8 | 98.10% |
z | zbyvmy | 317818893.5 | 348501915.1 | 91.20% |
p | wgxvux | 337370658.1 | 434917003.9 | 77.60% |
e | Symguyy | 552194562.3 | 849615786.6 | 65.00% |
c | Fuzxxpzuxny | 90712588.3 | 647376473 | 14.00% |
c | Hypzuxns (Guwvp) | 90563575.8 | 649389995.9 | 13.90% |
c | zuxxpuz | 77627165.8 | 647376473 | 12.00% |
c | Mwnw ymbwuyx | 64144744.4 | 647376473 | 9.90% |
c | Cvpynwx | 56665595.3 | 594481668.2 | 9.50% |
y | xcznduz | 154017865 | 1874264440 | 8.20% |
y | Gzuvvs/Gzuvvmyp | 146135531.3 | 1874264440 | 7.80% |
x | x | 108636862.4 | 3825988529 | 2.80% |
x | uxpxdxu/uxpznphyu | 59963929.1 | 3825988529 | 1.60% |
x | Chwuxb | 56673293.1 | 3825988529 | 1.50% |
x | wmzcwu | 49414747.6 | 3825988529 | 1.30% |
x | uwzwu | 46715174.6 | 3825988529 | 1.20% |
d | wpHyu Buznd Mzukyps | 444263957.9 | 0 | |
m | wpHyu Buznd Mzukyps | 384095375 | 0 | |
u | wpHyu Buznd Mzukyps | 291101111 | 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
expected output
"Product" | "Brand" | "Sales" | "Total Sales of Brand" | "% contribution" |
c | Hypzuxns (Guwvp) | 90563575.8 | 649389995.9 | 13.90% |
d | wpHyu Buznd Mzukyps | 444263957.9 | 0 | |
e | Symguyy | 552194562.3 | 849615786.6 | 65.00% |
m | wpHyu Buznd Mzukyps | 384095375 | 0 | |
p | wgxvux | 337370658.1 | 434917003.9 | 77.60% |
q | Hvuxw | 462862449.5 | 471814216.8 | 98.10% |
u | wpHyu Buznd Mzukyps | 291101111 | 0 | |
x | x | 108636862.4 | 3825988529 | 2.80% |
y | xcznduz | 154017865 | 1874264440 | 8.20% |
z | zbyvmy | 317818893.5 | 348501915.1 | 91.20% |
Thanks,
Ananth
Solved! Go to Solution.
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 )
)
Best Regards
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
Hi @Anonymous ,
OK. I'm looking forward to your feedback. 😀
Best Regards
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 )
)
Best Regards
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |