Calculating market shares across year and country

[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

[amitchandak]

@ys034 , when you want year wise

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

Year and brand wise (Brand in context/ visual), filter should work in above too

Grand Total Sales = CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),filter(ALLSELECTED('DataTable'), 'DataTable'[Year] = max('DataTable'[Year]))
,filter(ALLSELECTED('Item'), 'Item'[Brand] = max('Item'[Brand])) )

[ys034]

Thank you for your help on year level and country level it works like it is now when I want to include it on brands

As you can see in the screenshot, the market share goes to 100 percent as soon as I select a brand.

My Measure:

Grand Total Sales Brand = CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),filter(ALLSELECTED('DataTable'), 'DataTable'[Date].[Year] = max('DataTable'[Date].[Year])),FILTER(ALLSELECTED('DataTable'),'DataTable'[Brand] = MAX('DataTable'[Brand])))

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

[amitchandak]

@ys034 , if you add more levels or remove then use isinscope and make sure you include level above than that one

example

if(ISINSCOPE('DataTable'[Country]) ,  CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),filter(ALLSELECTED('DataTable'),'DataTable'[Country] =max('DataTable'[Country])   && year([Date]) = year(max([Date]))) ) , CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),filter(ALLSELECTED('DataTable'), year([Date]) = year(max([Date]))) ))

or

Grand Total Sales = if(ISINSCOPE('DataTable'[Country]) ,  CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),filter(ALLSELECTED('DataTable'),'DataTable'[Country] =max('DataTable'[Country])   && year([Date]) = year(max([Date]))) ) , CALCULATE(SUMX(VALUES('DataTable'),'DataTable'[Sales]),ALLSELECTED('DataTable'))) 

refer

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/