Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hanswittoeck
Helper I
Helper I

KPIs to color bar chart

Hi

in https://www.youtube.com/watch?v=zHzSBNES6jE it is explained how to use a measure to  color the bars in a bar chart.

 

 

this works fine, but i want to take it up to the next level 😊

I want to refer to a 'specs' table for this.

 
AreaLowerUpperColor
Garden020#0000FF
Garden2050#00FF00
Garden50100#FF0000

 

 

 

 

Is this possible?

Can anybody help me on the way?

 

thx

Hans

 

I just noticed it is not possible to add images in here

1 ACCEPTED SOLUTION

to be honest, @V-lianl-msft , I did not try your solution.

Reason: I found a solution myself:

ColorKPI = CALCULATE(
VALUES ( Specs[Color] ),
keepfilters(
filter(
specs,
specs[Lower] <= [NofOrders]
&& specs[Upper] > [NofOrders]
)
)
)
 
Does the trick. The sum problem is solved by creating a small table that contains the different areas.
 
What I have now is a working solution that allows to maintain the  specs per area. When you select anther area, the limit for the KPI change
ColorKPI2.jpg
 
 
 
 
 
 
 
 
 
 
 
 
If somebody is interested, I'm willing to share a PBIX on this.
 
Hans

View solution in original post

11 REPLIES 11
V-lianl-msft
Community Support
Community Support

Hi @hanswittoeck ,

 

Tre this measure:

Measure = SWITCH(TRUE(),
            MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>0&&MAX('Table'[Qty])<=20,"#0000FF",
            MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>20&&MAX('Table'[Qty])<=50,"#00FF00",
            MAX('Table'[Area])="Garden"&&MAX('Table'[Qty])>50&&MAX('Table'[Qty])<=100,"#FF0000")

It should be pointed out that when there is a legend field in the bar chart, conditional formatting is not possible.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thx @V-lianl-msft , but in my table next to Garden, there will be other entries. Your solution needs to be extended each time new entries are added to the specs table. I shouldd be able to FILTER in one or another way 

Hi @hanswittoeck ,

 

Maybe I misunderstood you. Please try this measure.

Measure = SWITCH(TRUE(),
            MAX('Table'[Qty])>0&&MAX('Table'[Qty])<=20,"#0000FF",
            MAX('Table'[Qty])>20&&MAX('Table'[Qty])<=50,"#00FF00",
            MAX('Table'[Qty])>50&&MAX('Table'[Qty])<=100,"#FF0000")

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?(Please mask any sensitive data before uploading)

It's better to share the results you expected, so that we can know more about the scenario.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@V-lianl-msft 

Sorry it took some time for me to answer. 

Intention is that the coloring happens automatically based on the values in SPECS.

If the sum of the 'NofOrders' is above lower and below upper the appropriate collor will be applied.

If you select another Functional_loc_L2, the intervals will change.

But it's not clear to me how i can share the pbix with you. I made a stripped version and saved it to the company onedrive but now i am stuck 😕

 

Hans

 

Hi @hanswittoeck ,

 

You may still need hard coding. As you said, the interval for each area is different, so you need to create different filter conditions for each area. Just like the previous formula.
You can share sample pbix through OneDrive links.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @V-lianl-msft 

i was hoping something like would do the trick

ColorKPI = CALCULATE
(
VALUES
(
Specs[Color] ),
FILTER
(
Specs,
Specs[Lower] <= ORDER_HEADER[NofOrders]
&& Specs[Upper] > ORDER_HEADER[NofOrders]
&& Specs[Area] = ORDER_HEADER[Area]
)
)
 
But I keep getting an error. The field ORDER_HEADER[Area] cannot be used in this expression and I don't understand why. It is just an additional filter to find the unique line that matches the criteria. Is there a similar way to find that unique record? on another one that works.
Hard coding is not an option. This visual needs to be used in different locations with differnt areas that all have different targets.
 
Your help is much appriciated.

Hello All,

I am getting closer to the solution

ColorKPI2 = CALCULATE (
VALUES ( Specs[Color] ),
FILTER
(
Specs,
Specs[Lower] <= ORDER_HEADER[nofOrders]
&& Specs[Upper] > ORDER_HEADER[nofOrders]
&& Specs[Area] = ORDER_HEADER[Area]
))
is finally a working formula that returns a value. However, there is still 1 problem:
nofHeaders is a measure in my visual that I use to count the number of orders, now the problem is (as far as I can figure out) that the calculation of the ColorKPI happens before the total nofOrders is calculated. On a row level nofOrders is always 1, it is only at aggregated level this should be checked.
@amitchandak , @V-lianl-msft  any idea how i can do this?
Or someone else?
 

Hi @hanswittoeck ,

 

Try 

sum_nofoeders= CALCULATE (
SUM ( ORDER_HEADER[nofOrders] ),
ALLEXCEPT(ORDER_HEADER,ORDER_HEADER[Area])
)

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

to be honest, @V-lianl-msft , I did not try your solution.

Reason: I found a solution myself:

ColorKPI = CALCULATE(
VALUES ( Specs[Color] ),
keepfilters(
filter(
specs,
specs[Lower] <= [NofOrders]
&& specs[Upper] > [NofOrders]
)
)
)
 
Does the trick. The sum problem is solved by creating a small table that contains the different areas.
 
What I have now is a working solution that allows to maintain the  specs per area. When you select anther area, the limit for the KPI change
ColorKPI2.jpg
 
 
 
 
 
 
 
 
 
 
 
 
If somebody is interested, I'm willing to share a PBIX on this.
 
Hans
amitchandak
Super User
Super User

@hanswittoeck , I have not tested . Try a measure like this and return you color code colum

Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")

 

and use that in conditional formatting with the field option

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

Thank you for your reply @amitchandak .

The coloring as such works, my major problem is refer to the 'specs' table to find the correct value. What i need is in plain language

colorKPI =  if specs.area =area of current record 

                                 if  Qty  > specs.Lower and QTY < specs.Upper

                                then specs.Color

But i cannot 'translate' this into DAX.

 

Hans

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.