topic Re: Fixed Chart Comparator in DAX Commands and Tips

Fixed Chart Comparator

Mat42 — Mon, 21 Sep 2020 13:20:19 GMT

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.

Re: Fixed Chart Comparator

lbendlin — Mon, 21 Sep 2020 23:23:10 GMT

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.

Re: Fixed Chart Comparator

Mat42 — Wed, 23 Sep 2020 10:55:56 GMT

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?

Re: Fixed Chart Comparator

lbendlin — Wed, 23 Sep 2020 11:11:14 GMT

Change the relationship to 1:M from Names to Data. Then change the measure to compute the company average properly as I mentioned initially.

Re: Fixed Chart Comparator

Mat42 — Thu, 24 Sep 2020 10:50:03 GMT

lbendlin Perfect!

Thanks a lot.