cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AJ_Clark Frequent Visitor
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): 

 

BrandMMYYYValue ($)UnitsRegion
Brand A01/2017 2270932Region A
Brand A02/2017 3745182Region A
Brand A03/20172075755Region B
Brand A04/2017798104Region A
Brand A05/2017 6200002Region C
Brand B01/20173649181Region A
Brand B02/20173291481Region A
Brand B03/20174256983Region B
Brand B04/20171478562Region A
Brand B05/20172986586Region C
Brand C01/20173268521Region D
Brand C02/20177459623Region C
Brand C03/20178796514Region C
Brand C04/20171993653Region D
Brand C05/20172045672Region D
Brand D01/20172369893Region A
Brand D02/20173566121Region A
Brand D03/20176549861Region B
Brand D04/20175425895Region A
Brand D05/20174526981Region C
Brand E01/20171253652Region A
Brand E02/20179652142Region A
Brand E03/20176500041Region B
Brand E04/20174587592Region A
Brand E05/20173557883Region C
Brand F01/20178424252Region D
Brand F02/20175212451Region C
Brand F04/20175454452Region D
Brand F05/20179864521Region 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:

 

1.PNG

And the following line chart: 

2.PNG

 

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: 

 

4.PNG

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: 

 

3.PNG

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):

 

5.PNG

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...

 

6.PNG

 

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
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.
AJ_Clark Frequent Visitor
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. 

 

AJ_Clark Frequent Visitor
Frequent Visitor

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

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