cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Market Share Calculation - Filtered value line chart challenges (DAX)

Hi everyone, what seems to be such a simple calcualtion is causing us some issues with the line chart visual and I'm hoping that there might be a simple solution which we are overlooking.

We are trying to display market share by brand, (by month), in a line chart that can update when additional filters are applied. THe line chart is working fine when there are no filters applied (the values match the table and the line chart) but as soon as we filter the data (e,g, lets view share in regions a, b or c or within price point x, y or z) the values in the line chart go crazy, whereas the data in the table / matrix updates fine.

Sample Data

In reality, each month has hundreds of entries by brand with each sale being a unit (one row per sale), but for simplicity the same calculation should be able to work on the data set detailed below (I've simply added additioanl units per row to keep the data set small):

 Brand MMYYY Value (\$) Units Region Brand A 01/2017 227093 2 Region A Brand A 02/2017 374518 2 Region A Brand A 03/2017 207575 5 Region B Brand A 04/2017 79810 4 Region A Brand A 05/2017 620000 2 Region C Brand B 01/2017 364918 1 Region A Brand B 02/2017 329148 1 Region A Brand B 03/2017 425698 3 Region B Brand B 04/2017 147856 2 Region A Brand B 05/2017 298658 6 Region C Brand C 01/2017 326852 1 Region D Brand C 02/2017 745962 3 Region C Brand C 03/2017 879651 4 Region C Brand C 04/2017 199365 3 Region D Brand C 05/2017 204567 2 Region D Brand D 01/2017 236989 3 Region A Brand D 02/2017 356612 1 Region A Brand D 03/2017 654986 1 Region B Brand D 04/2017 542589 5 Region A Brand D 05/2017 452698 1 Region C Brand E 01/2017 125365 2 Region A Brand E 02/2017 965214 2 Region A Brand E 03/2017 650004 1 Region B Brand E 04/2017 458759 2 Region A Brand E 05/2017 355788 3 Region C Brand F 01/2017 842425 2 Region D Brand F 02/2017 521245 1 Region C Brand F 04/2017 545445 2 Region D Brand F 05/2017 986452 1 Region D

Using Our DAX, which is most likely wrong as you'll see where it fall down shortly, we get the following data in our matrix:

And the following line chart:

All good so far. Picking a single brand works fine too, the line chart updates accordingly and the values reflect what is shown in the table:

Now, the brands operate in different market segments (regions and price points) so if we want to see the market share where the price range is between (inclusive) 200,000-400,000 we simply apply that filter and the table and line chart behave fine, until you select a brand from the table:

If we want to see Brand A and B plotted on the line chart and we either filter them or select them from the table, the line chart goes haywire (even tthough the table is showing the data as we want it):

If I load the same measure into a line / column chart combo (using just the line value) the visual matches the table, but I can;t chart multiple brands...

So my question is what would the correct DAX expression be to allow for market share to be calcualted and charted correctly (as per the first example) where no additioanl filters are applied, price filters are applied and or location filters are applied (or any combination of other filters)?

If it makes any difference. in the example given, all data resides in one table, where as in practice our date table is separate so the query would need to allow for that quirk too.

Thanks a lot for any help which can be provided!

3 REPLIES 3
Community Support Team

## Re: Market Share Calculation - Filtered value line chart challenges (DAX)

Hi @AJ_Clark,

What measures did you use? How did you calculate the percentage values displayed in Matrix and line chart?

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Market Share Calculation - Filtered value line chart challenges (DAX)

@v-yulgu-msft, Thanks for your help! This really isn't my area of expertise, so excuse the laughable query (our developer is on leave for the week so I'm trying to self-serve).

The market share value is derrived by dividing two measures. Lets call them [sales] and [all sales] and presenting the value as a %

For [sales], it's just the sum of units

For [all sales] its the sum of all units, all brands (All Sales = CALCULATE(SUM(Sheet1[Units]),ALL(Sheet1[Brand]))- I'm aware that this is the measure which needs fixing. I need this value to be the sum of all sales in a specific month (the same number for all brands) but also have it adapt to both price point filters and location filters. I've tried a bunch of 'all' and 'all except' inclusions in my calculation but I'm yet to get a result that works...

Getting the value to work in the table / matrix is no problem. I just can't seem to get it to chart in a line chart (when I add a location or price point filter)  As soon as I select a brand to highligh the individual trend I get 100% values for each month.

Frequent Visitor

## Re: Market Share Calculation - Filtered value line chart challenges (DAX)

@v-yulgu-msft did you have any thoughts at all?