calculating market share over years and countries

[ys034]

Dear all,

I have 2 big problems with my market share dashboard and I am sure many market analysts faced this problem before, so I am really hoping for any advice.

I created a sample file to keep things simple.

We have sales data of:

3 Brands (a, b, c) in 2 Countries (Germany, France),  across 2 sales channels (online, offline), 2 Product Categories (1, 2) & from 2 years (2020, 2021).

So for example, sales for Germany in 2021 looked like this:

Now we want to see the market shares of each of the 3 brands across different market splits:

Our measures so far are:

Total Sales = SUM('DataTable'[Sales])

Grand Total Sales = CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),ALLSELECTED('DataTable'))

MarketShare = CALCULATE([Total Sales]/[Grand Total Sales])

Now we have a matrix table and some slicers to control the other dimensions:

This gives us the correct market shares for each brand in Germany in 2021 (Brand A has 37% market share in 2021). This works also fine if we want only the offline or online market shares (or respective Product Group shares).

Problem No.1:

But...when we choose both years to compare the market shares over time or when we want to see both countries, the market shares become all false:

The reason is that Power BI wrongly now takes both years together (or in the 2nd example both countries) as the respective total market and does not distinguish between the years and/or countries.

Can you please help?

Problem No.2:

Another issue for our final market share dashboard is the following. Suppose we are brand a, and we want to see our market shares only.

With the current setting it would just show 100% market share for each Country/Year because Power BI does not know that it needs to take all brand sales into account:

Thank you all very much in advance for your help!

The sample data can be downloaded here:

https://www.dropbox.com/s/l6rc3jqph1uwdf2/Market%20Share%20Test%20Data.pbix?dl=0

[v-chenwuz-msft · Accepted solution]

Hi @ys034 ,

Problem 1 and 2:

change Grand Total Sales as the following:

Grand Total Sales =
CALCULATE(
    SUMX( VALUES( 'DataTable' ), 'DataTable'[Sales] ),
    REMOVEFILTERS( 'DataTable'[Brand] )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

Thank you for providing the sample data.

Please read about ALLEXCEPT and REMOVEFILTERS. That will help you to shape your filters as needed for each of the questions. Also note that CALCULATE and SUMX are (sort of) doing the same thing, and that measures use an implicit CALCULATE.