cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BSLATTER Regular Visitor
Regular Visitor

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

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?
1 ACCEPTED SOLUTION

Accepted Solutions

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

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.


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4

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

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.


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

BSLATTER Regular Visitor
Regular Visitor

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

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

Highlighted

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

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.
BSLATTER Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,672)