Forum Discussion
Plot a filtered value compared to an average across unfiltered values
Here's how I solved it. Instead of separate measures for each company I just created one measure that's the result of the highlighted company:
SalesSelected = CALCULATE(AVERAGE([sales]), ALLSELECTED(sales[company]))
The average line is the same as chrisu suggested:
AvgSales = CALCULATE(AVERAGE([sales]), ALL(sales[company]))
Thanks chrisu for puting me on the right track.
Responsive ResidentThere's probably a better solution than this, but this has worked for me:
1. Because you can either have a legend OR multiple measures on a line chart, you will have to define a measure for each company, like this:
CompanyASales = CALCULATE(SUM([sales]), [company] = "CompanyA")
CompanyBSales = CALCULATE(SUM([sales]), [company] = "CompanyB")
etc.
2. Then create an average sales measure using ALL to override any filters on company:
AverageSales = CALCULATE(AVERAGE([sales]), ALL([company]))
3. Add all of those measures to the values area of the line chart (you will have to remove company from the legend first).
Hi chrisu, thanks for responding - I was excited when I saw your post as I thought "Yes, that's going to work!". But I don't think it quite does what I wanted. It gets me lines for each company on the chart plus a line for the average across all companies on the same chart which is great and a step further that I'd gotten before (kudos!). I was hoping though that when I filter by CompanyA by clicking on the histogram then ONLY CompanyA AND the average line would remain on the plot, allowing the user to focus on just the difference between the company of interest and the average. Any ideas if this is possible? Here's a pic of where I'm at now.
Here's how I solved it. Instead of separate measures for each company I just created one measure that's the result of the highlighted company:
SalesSelected = CALCULATE(AVERAGE([sales]), ALLSELECTED(sales[company]))
The average line is the same as chrisu suggested:
AvgSales = CALCULATE(AVERAGE([sales]), ALL(sales[company]))
Thanks chrisu for puting me on the right track.