Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
Any idea? do I need to change the measures?
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |