Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating market shares across years and countries

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:

NittyGritty_2-1641832204148.png

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:

NittyGritty_0-1641832174677.png

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:

NittyGritty_3-1641832310072.png

 

NittyGritty_4-1641832349849.png

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:

NittyGritty_5-1641832424557.png

 

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Create a Brand dimension (with the distinct brand values) and relate that to the main table.

Use this brand dimension in the matrix and slicer.

Alter the Grand Total Sales measure to this:

Grand Total Sales = CALCULATE([Total Sales], REMOVEFILTERS(Brand[Brand]))

Also remove the Calculate from MarketShare.

----

I think that'll do it but haven't tested it extensively.

 

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

Create a Brand dimension (with the distinct brand values) and relate that to the main table.

Use this brand dimension in the matrix and slicer.

Alter the Grand Total Sales measure to this:

Grand Total Sales = CALCULATE([Total Sales], REMOVEFILTERS(Brand[Brand]))

Also remove the Calculate from MarketShare.

----

I think that'll do it but haven't tested it extensively.

 

Anonymous
Not applicable

Any idea? do I need to change the measures?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.