Forum Discussion

junoglasgow's avatar
junoglasgow
Frequent Visitor
8 years ago

Market share by cateogory

  I wish to make a Bar chart only showing specific brand M/S by category. Dataset consists as following;   Data name: Market Column1: 5 Market Segment category Column2: Brand with the list of se...
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi junoglasgow

From your another post which is related to this one,

https://community.powerbi.com/t5/Desktop/Market-share-by-category/m-p/538667

 

How do you calculate the percentage?

 Chart1 BrandA    Chart2 BrandB   Chart3  BrandC    Chart4 BrandD
Entry 12%   Entry 13%   Entry 14%   Entry 11%
Main 13%   Main 15%   Main 13%   Main 10%
High 22%   High 24%   High 24%   High 27%
Premium 1%   Premium 2%   Premium 11%   Premium 11%
Luxury 6%   Luxury 5%   Luxury 62%   Luxury 16%

 

I make a test based on your table as below, but it seems not like yours.

Measure-> total Qty per Brand and per Market Segment, for example, for Luxury, total Qty for Brand A is 11

Measure2-> total Qty per Market Segment, for example, for Luxury, total Qty for all Brands is 29

Measure3->Measure/Measure2

 

Also Rank1 Brand~Rank4 Brand, and Brand A market share 74% in Entry / Brand A Market share 34% in Luxury / 85% market share in Premium are not clear to me.

(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..)

 

Could you tell me how to calculate these percentage?

 

Best Regards

Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi junoglasgow

    Create measures to get the percentage you'd like

    per brand = CALCULATE(SUM(Sheet1[Qty Sold]),ALLEXCEPT(Sheet1,Sheet1[Brand]))

PerMarket PerBrand = CALCULATE(SUM(Sheet1[Qty Sold]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Market Segment]),[Brand]=MAX([Brand])))

MarketShare(%) ByBrand = [PerMarket PerBrand]/[per brand]

    How do you create the bar chart? Is my bar chart right? This means which columns or measures you want to add in fields of bar chart?

     

     

    Best Regards

    Maggie