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
ys034
Helper I
Helper I

calculating market share over 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:

ys034_0-1641893762843.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:

ys034_1-1641893762922.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:

ys034_2-1641893762866.png

 

 

ys034_3-1641893762925.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:

ys034_4-1641893762891.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
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1642130098264.png

vchenwuzmsft_1-1642130123011.png

 

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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:

vchenwuzmsft_0-1642130098264.png

vchenwuzmsft_1-1642130123011.png

 

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
Super User
Super User

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.  

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.