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?
I'm trying to get an aggregate of the totals (based on our filters).
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
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?
* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
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.