I have a dataset with sales data for some time:
| Company | SubBrand | Analytical Name | Sales | Market |
|---|---|---|---|---|
| Company A | Brand 1 | Product 1 | Num. | Market A |
| Company A | Brand 1 | Product 2 | Num. | Market B |
| Company B | Brand 2 | Product 3 | Num. | Market A |
| Company C | Brand 3 | Product 4 | Num. | Market A |
etc.
I have to create a report that shows the market share of some specific things based on selected slicers.
I have two static slicers, to control which market to analyze (A,B...), and a slicer to show the category of analyzed data (Company, SubBrand, Analytical Name).
Based on those two selections, I've created a dynamic slicer that allows to select categories from 2nd slicer based on the market from the first, e.g.:
Slicer1 -> Market A
Slicer2 -> Company
Slicer3: Company A, Company B, Company C
If, instead, Slicer2 was selecting SubBrand, Slicer3 shows Brand1, Brand2, etc.
Now, the market share of each category is just [Num. sales for the selected category]/[Num. sales in selected market]. Simple.
I have a lineplot that plots market share over time, with legend being categories selected in Slicer3.
I need to add an additional dataline "Not selected"/"Others", that would show the market share of those categories not currently selected, e.g.:
Slicer3 -> Company A
lineplot: Market Share of Company A, Market share of both Company B and C.
Because it's a lineplot, I can't just create a measure that would calculate the Market Share of those not selected, because you can't put measures in a legend of a lineplot.
I can't create a dynamic table that automatically group categories based on my selection in slicers, because DAX doesn't support dynamic tables of that sort.
Is there a solution to this?
