Create a legend good/bad based on a measure for use on a bar chart

[BSLATTER]

Hello,

Dataset: Salespeople and the different transactions associated to them

Visual: Horizontal bar chart with a hierarchy. Displays a couple different KPIs based on a Chiclet slicer. IE: Sales, Margin.

Request: Colour the bar chart with "better than last year" or "worse than last year" (to start)'. 

So far I have two calculations, one that is for current year and one for last year like this:

KPISwitch = SWITCH(SELECTEDVALUE('KPI'[KPI]),

"Sales", [YTD CY Sales],

"Gross Margin (%)", IFERROR([YTD CY Gross Margin %]*100,BLANK()),

"Gross Margin ($)", [YTD CY Gross Margin],

)

Last year is the same, but for LY calculations. All three of those calculations involve aggregating the calculation.

I can create a measure that looks like this:

KPIColour = 

IF([KPISwitch]=[KPISwitchLY],"Unchanged", IF([KPISwitch]<[KPISwitchLY], "Better", "Worse")))

But I can't use it as a legend on the bar chart. If I create the same calculation as a column it doesn't error out, but the result is always "Unchanged", implying at least that it's returning null for each of the KPISWITCH calculations.

Objective: Return a flag (good/bad, or even 0 or 1) if salesperson metric was better or worse this year than an equivalent time frame last year and be able to colour a bar chart with it. Any ideas?

[MattAllington · Accepted solution]

What I would try is this.

1. write a measure that returns the values that are better than last year, or blank
2. write another measure that returns the values that are better than last year, or blank.
3. Add these both to a stacked bar chart and colour the bars to you liking.

[BSLATTER]

I'm trying to get an aggregate of the totals (based on our filters).

For example:

Tom and Alice are salespeople with 100 and 155 transactions respectively. Tom had 25 in March 2019 totalling $100, Alice had 15 in March 2019 totalling $150. In March 2018 Tom had done $110, and Alice had done $140.

I'd like the bar chart to show - when I filter on March, 100 and 150, with the 100 coloured red, and the 150 coloured green (for example to show good and bad).

There isn't really a specific value per row to show better than last year, as they're all just transactions and it depends on the aggregate.

We also do filters on things like location, etc that would have to be respected in this colouring

[MattAllington]

I don't see any changes in my suggestion based on you comments. Write 2 sets of measures as I described and add them as stacked bar charts.  If you can't write measures that indentify good and bad, how can you colour them anyway?

[BSLATTER]

Hey,

Ah yes this works! I didn't understand your solution before and responded a little too quickly. I was hoping to get it to colour based on the aggregation being better/worse, but creating two aggregations (one better, one worse) does accomplish the same goal.

I'll mark your solution as correct