Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have data that essentially looks like this:
Company_Label | Product_Label | COUNT |
John's Apples | Red Delicious | 1 |
John's Apples | Honeycrisp | 1 |
Industry Total | Red Delicious | 1 |
Industry Total | Honeycrisp | 1 |
Industry Total | Red Delicious | 1 |
Industry Total | Red Delicious | 1 |
Industry Total | Honeycrisp | 1 |
Industry Total | Red Delicious | 1 |
The apples that the company "John's Apples" sells get reported listed under John's Apples, but also as part of the industry total (essentially the first 4 rows are the 2 apples that John's Apples sold). I want to create a market share chart for John's Apples, but if I create it as is, the numbers will be skewed as it will show John's Apples vs. Industry Total (2 of 😎 instead of John's Apples vs. Everyone Else (2 of 6). In excel, I could create a simple calculated item in a PivotTable and chart that, but I can't figure out how to do it in Power BI.
Solved! Go to Solution.
Hi,
You could try below measure
Measure = IF ( MIN ( t1[Company_Label] ) = "John's Apples", CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ), COUNT ( t1[Company_Label] ) - CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi DannyPhillips,
In your scenario, you said you want to get John's Apples vs. Everyone Else (2 of 6), what did you mean of “everyone”? Did you want to get John's Apples vs. Industry Total(2:6)? If so, you could just create measure Measure 3 = COUNT(sharechart[Product_Label]), it will return result like below
If this is not what you want, could you please inform me in details(such as your expecting output and your detailed sample data)? Then I will help you more correctly.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the response, the issue is that your example table shows a grand total of 8, but the industry total is already the grand total. What I really would like is to be able to chart the results like this:
but I don't have a "competitor" company label, I just know it is the sum of industry total minus the sum of John's Apples. I would also like a simple table like this:
but I'm unsure of how to create that.
Hi,
You could try below measure
Measure = IF ( MIN ( t1[Company_Label] ) = "John's Apples", CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ), COUNT ( t1[Company_Label] ) - CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for the solution! I changed it to the below to accomodate if the count was ever more than 1. This appears to be working.
Measure =
IF (
MIN ( t1[Company_Label] ) = "John's Apples",
CALCULATE ( SUM ( t1[COUNT] ), t1[Company_Label] = "John's Apples" ),
SUM ( t1[COUNT] )
- CALCULATE ( SUM ( t1[COUNT] ), t1[Company_Label] = "John's Apples" )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |