Market share(%) by category

[junoglasgow]

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]

I fixed the example table, now they are in line with with data

Can Anyone help me out this, please?

[PattemManohar · Accepted solution]

@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

[PattemManohar]

@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 ? 

[junoglasgow]

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

[junoglasgow]

@PattemManohar

Appreciate your help, I Try it but it keep saying that "The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value.

I try this "New measure" option

[PattemManohar]

@junoglasgow As mentioned above, please try using "New Table" option

[junoglasgow]

@PattemManohar

I made it as guided but it only shows % of Grand total. I want to see % of subtotal for a specific brand.

For example, Brand A market share 74% in Entry / Brand A Market share 34% in Luxury / 85% market share in Premium etc..