Exclude second plotted line from filter on line chart

[RG_HC]

Hi all

I have some data averages that I have plotted on six separate line charts, with a custom filter that filters the result by Customer (there are 35 customers).

I'd like to add a second line to each line chart which shows the overall average, that IS NOT affected by the visual filter. This will give the client a view of how they are doing (when they select a customer by ID) of how that customer is tracking against the overall average.

Is there any way to do this?

[Ashish_Mathur]

Hi,

Write a measure like this

=CALCULATE([your measure],ALL(Data[Customer]))

Hope this helps.

[RG_HC]

Thanks all; all work in their way however the issue is that I have 35 lines on the line chart that I use the filter visualisation for. So the customer can click any one or several customer IDs to see selected customer lines on the chart. In order to show the default of all, I need to have the Legend active, which removes the ability to add a second item under value, which is what I'd need to get this to work. 

The only way I see this working (to my way of thinking) is if there was someway to create a custom ROW that had these averages for each category, which could be included in the filter as a selectable option. Has anyone heard of doing this before?

[MFelix · Accepted solution]

Hi @RG_HC,

You can do that on the query editor making the addtional line and adding it to your model as a single row, however you would need to filter that line out from other visuals / reports or else your results would always be impacted by that.

Regards,

MFelix

[MFelix]

Hi @RG_HC,

Not really sure how you are getting the information you need if trough measure o calculation in visual, but you can do the following.

• Create two or more measure to use in your charts ( the only way to add additional lines in the line charts is to have measure and nothing on legend)
• My measure are as follow:

• Average_Blue = CALCULATE(AVERAGE(Data[Value]); Data[Color] = "Blue")
  
  Average_Total = CALCULATE(AVERAGE(Data[Value]);ALL(Data[Color]))

  the first one I repeat per colour, but again this is just a mockup data not sure how yours is setup and the second one is the important one because adding the filter in the CALCULATE with ALL it ignores the filters you have on your measure and calculates to the overall data so the final result is below

As you can see the black line that refers to Total Average is always the same so having comparision with the different colours.

Regards,

MFelix