How to get correct MS% in data matrix for Category and Subcategory?

[Anonymous]

Hi at all,

how do I get the correct market share for the category and the subcategory in a data matrix in PBI?

As you can see from the image below, the market share from my company vs all other competitor is calculated correctly (called Category 1, including the two values "own company" & "total competitor"). The Subkategory (called PIP) has two value ("PIP Products" & "Non-PIP Products"). By now, the market share of the subcategory is calculated vs. all other of its value (e.g. own company --> non-pip products vs. total competitor --> non-pip products). 

But I want to calculate the market share inside the category1, so it should be own company: non-pip vs. pip. 

Do you have any ideas on how to modify my measure so that it calculates the market share correctly for both, category 1 and the subcategory PIP?

My current measures are:

MS% = 

var A= [Sum of Value]

var B= CALCULATE([Sum of Value],ALL('table'[category1]))

Return

DIVIDE(A,B)

Sum of Value = CALCULATE(SUM([Value]))

Thank you in advance!

Kind regards

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may craete two measure as below.

Sum = SUM('Table'[Value])

MS% = 
DIVIDE(
    SUM('Table'[Value]),
    CALCULATE(
        SUM('Table'[Value]),
        ALLEXCEPT('Table','Table'[Category 1],'Table'[Date])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try

MS% =
var A= [Sum of Value]
var B= CALCULATE([Sum of Value],Removefilters('table'[month year])) //removed on on column
Return
DIVIDE(A,B)

or

MS% =
var A= [Sum of Value]
var B= CALCULATE([Sum of Value],allselected('table'[month year])) //removed on on column
Return
DIVIDE(A,B)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

It looks like I can´t share files in this forum, so I post the rawdata as a table in this comment. Hope this still fits.

Market Category1 PIP Date Value
Market 1 Own Company NON-PIP PRODUCTS 01.02.2018 00:00 500
Market 1 Own Company NON-PIP PRODUCTS 01.03.2018 00:00 600
Market 1 Own Company NON-PIP PRODUCTS 01.04.2018 00:00 550
Market 1 Own Company PIP PRODUCTS 01.02.2018 00:00 100
Market 1 Own Company PIP PRODUCTS 01.03.2018 00:00 120
Market 1 Own Company PIP PRODUCTS 01.04.2018 00:00 90
Market 1 total competitor NON-PIP PRODUCTS 01.02.2018 00:00 7000
Market 1 total competitor NON-PIP PRODUCTS 01.03.2018 00:00 7300
Market 1 total competitor NON-PIP PRODUCTS 01.04.2018 00:00 6800
Market 1 total competitor PIP PRODUCTS 01.02.2018 00:00 495
Market 1 total competitor PIP PRODUCTS 01.03.2018 00:00 573
Market 1 total competitor PIP PRODUCTS 01.04.2018 00:00 780

Kind regards