Forum Discussion
Fixed Chart Comparator
This feels like it should be simple, but for the life of me I can't work out how to do it.
Let's say I have a very simple table containing the following entries:
| Name | Sales Figures | Yearly Total |
| Dave | 123 | 1119 |
| Susan | 223 | 1436 |
| Linda | 578 | 2007 |
| Mike | 342 | 1996 |
| Steve | 111 | 2015 |
| Company Average | 413 | 2134 |
All I want to create is a simple, 2 bar horizontal bar graph to compare staff details against the Company Average. The slicer will contain staff names and when I choose a name, I want it to display the Company Average as a fixed comparator so that it will always be visible on the chart, but the staff name will change with the slicer.
I've attached a couple of example images.
I've tried various things and I just can't work it out...which feels stupid because it must be simple.
Any help would be greatly appreciated.
You don't need to include the Company average in your data if you can also compute it on the fly. But if that is not possible then you can still make it work by using a separate measure.
Sales Average := CALCULATE(sum(Performance[Sales Figures]),Performance[Name]="Company Average")Then add the Sales Figures value and the new measure to the Values well.
4 Replies
You don't need to include the Company average in your data if you can also compute it on the fly. But if that is not possible then you can still make it work by using a separate measure.
Sales Average := CALCULATE(sum(Performance[Sales Figures]),Performance[Name]="Company Average")Then add the Sales Figures value and the new measure to the Values well.
lbendlin, that's brilliant and works as I need it to. Quick related follow-up question:
Because this is part of a larger report I'm slowly building up, I'm adding new sections every day and I now have 2 tables: the simple data table I explained before, and a new table called 'Names' that I've created to only contain (unsurprisingly) staff names. I've joined the Names table to the data table using a one-to-one relationship.
If I follow your instructions, then produce a slicer containing staff names using the original data table, the chart displays correctly. However, if I create a slicer using the new Names table the Sales Average data disappears off the chart.
Is there something that needs changing in the measure?
Change the relationship to 1:M from Names to Data. Then change the measure to compute the company average properly as I mentioned initially.
