Forum Discussion
Market share(%) by category
Hi
I am trying to make the bar charts show Market share by each market segment. I have looked up all post but could not find a good case I am looking for. It would be much appreciated if you could help me to work out!
Condition: There are 5 market segments and multiple brands competing in each market segment. The data table is as below
| Market Segment | Brand | Qty Sold |
| Entry | A | 2 |
| Main | B | 3 |
| High | C | 11 |
| Premium | D | 23 |
| Luxury | E | 11 |
| Entry | E | 2 |
| Premium | D | 33 |
| Luxury | A | 11 |
| Luxury | C | 3 |
| Luxury | C | 4 |
| High | C | 1 |
| High | F | 2 |
| Entry | E | 3 |
| High | C | 11 |
| Entry | A | 9 |
| Entry | G | 2 |
| Entry | H | 2 |
| Entry | I | 2 |
| High | C | 1 |
| Entry | F | 2 |
| Entry | E | 3 |
| High | C | 11 |
| Entry | A | 9 |
Question
1. I want to make a bar chart shows category market share(%) by brand as below (in a bar chart)
| Chart1 | BrandA | Chart1 | BrandB | Chart1 | BrandC | Chart1 | BrandD | |||
| Entry | 65% | Entry | 0% | Entry | 0% | Entry | 0% | |||
| High | 0% | High | 0% | High | 83% | High | 0% | |||
| Luxury | 35% | Luxury | 0% | Luxury | 17% | Luxury | 0% | |||
| Main | 0% | Main | 100% | Main | 0% | Main | 0% | |||
| Premium | 0% | Premium | 0% | Premium | 0% | Premium | 100% |
2. I want to make a bar chart shows category market share(%) by brand and by Ranking
* Ranking: Ranking based on total number of Qty sales in total (Sum of all product category: Entry, Main, High, Premium, Luxury)
| Chart1 | BrandD | Chart1 | BrandC | Chart1 | BrandA | Chart1 | BrandE | |||
| Entry | 0% | Entry | 0% | Entry | 65% | Entry | 42% | |||
| High | 0% | High | 83% | High | 0% | High | 0% | |||
| Luxury | 0% | Luxury | 17% | Luxury | 35% | Luxury | 58% | |||
| Main | 0% | Main | 0% | Main | 0% | Main | 0% | |||
| Premium | 100% | Premium | 0% | Premium | 0% | Premium | 0% |
junoglasgow Please try below:
Add "New Table"
MarketBrandOutput = SUMMARIZE(MarketBrand,MarketBrand[Brand],MarketBrand[MarketSegment],"MarkSegTotal",SUM(MarketBrand[Qty]))
Add two additional fields using "New Column" option as below
BrandTotal = CALCULATE(SUM(MarketBrandOutput[MarkSegTotal]),FILTER(ALL(MarketBrandOutput),MarketBrandOutput[Brand]=EARLIER(MarketBrandOutput[Brand])))
Share% = DIVIDE(MarketBrandOutput[MarkSegTotal],MarketBrandOutput[BrandTotal])
Finally, your output will look like this...
Just representing the above data in Matrix visual, for your reference
8 Replies
- PattemManohar
Community Champion
junoglasgow Just to confirm, the expected output (the last two tabular data) is not aligned with the sample data. It just sample layout that you are expecting isn't it ?
PattemManohar appreciate your reply, You are right. I created tabular tables just to demonstrate how the bar chart should look like. these are not aligned with the sample data.
- PattemManohar
junoglasgow Thanks for confirming that.
Please try this for your 1st Requirement, by using "New Table" option
MarketShareByBrand = SUMMARIZECOLUMNS(MarketBrand[MarketSegment],MarketBrand[Brand],"Total",SUM(MarketBrand[Qty]))
Then in the chart visual, change the "Value" field to "Show Value as -> Percent of Grand Total". You can have one chart and a slicer on Brand instead of having different charts for each brand. If you want different charts for each brand, then have a "Visual Level Filter"
I fixed the example table, now they are in line with with data
Can Anyone help me out this, please?
- PattemManohar
junoglasgow Please try below:
Add "New Table"
MarketBrandOutput = SUMMARIZE(MarketBrand,MarketBrand[Brand],MarketBrand[MarketSegment],"MarkSegTotal",SUM(MarketBrand[Qty]))
Add two additional fields using "New Column" option as below
BrandTotal = CALCULATE(SUM(MarketBrandOutput[MarkSegTotal]),FILTER(ALL(MarketBrandOutput),MarketBrandOutput[Brand]=EARLIER(MarketBrandOutput[Brand])))
Share% = DIVIDE(MarketBrandOutput[MarkSegTotal],MarketBrandOutput[BrandTotal])
Finally, your output will look like this...
Just representing the above data in Matrix visual, for your reference
