Market share in hierarchy

[anettbaksa]

Hello, 

I have a thousand product in hierarchy something like this: 

Category
Subcategory
Manufacturer
Brand 
Subbrand
Product 

I would like to calculate market share (%) what based always on the Subcategory, so subcategory is the 100% in all level. For example the product market share has to be the proportionate % of the Subcategory. Subbrand, Brand and Manufacturer is the same situation like product. After I have to show this market shares in matrix. The rows contains the product hierarchy and the only column contains the market share in %. 

Can anybody help me with the calculation? 

Thank you, 

Anett 

[v-lili6-msft]

hi  @anettbaksa 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[anettbaksa]

Hi @v-lili6-msft 

I made sample data. The dataset is the following:

And I have to show it in matrix like that:

The subcategories have to be always the 100%. For example look the "Tablets" subcategory.  A and B brand create the 100% of tablets market share. That's usual market share calculation. But after this level the logic is change. Brand1 and Brand2 is also create the 100% of tablets market share. Brand 1- dark, Brand 1- milk, Brand 1- white and Brand 2 (this is not brand, but subbrand level because this product don't have subbrand level, in this case we use the brand name again or empty) is also create the 100% of tablets. And product level is also the same. 

I hope this is understandable. If it's not, please ask. 

Thank you, 

Anett 

[v-lili6-msft · Accepted solution]

HI  @anettbaksa 

You could try the formula as below:

Measure = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Value in millio HUF] ) ),
    CALCULATE (
        SUM ( 'Table'[Value in millio HUF] ),
        ALLEXCEPT ( 'Table', 'Table'[subcategory] )
    ),
    0
)

or

Measure 2 = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Value in millio HUF] ) ),
    CALCULATE (
        SUM ( 'Table'[Value in millio HUF] ),
        FILTER(ALLSELECTED('Table'),'Table'[subcategory]=MAX('Table'[subcategory]))
    ),
    0
)

and here is sample pbix file, please try it.

Regards,

Lin

[anettbaksa]

Hello @v-lili6-msft , 

Thank you for the answer, it's working. 

Just one more question. 

If i want to show this market share for every month, how can I do it?

Because if I put the period in columns, it's not working. It split 100% for months. 

Thank you, 

Anett