cancel
Showing results for
Did you mean:
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.

 Area Lower Upper Color Garden 0 20 #0000FF Garden 20 50 #00FF00 Garden 50 100 #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
Helper I

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

If somebody is interested, I'm willing to share a PBIX on this.

Hans
11 REPLIES 11
Community Support

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.

Helper I

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

Community Support

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.

Helper I

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

Community Support

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.

Helper I

i was hoping something like would do the trick

ColorKPI = CALCULATE
(
VALUES
(
Specs[Color] ),
FILTER
(
Specs,
&& 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.
Helper I

Hello All,

I am getting closer to the solution

ColorKPI2 = CALCULATE (
VALUES ( Specs[Color] ),
FILTER
(
Specs,
&& 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?

Community Support

Try

``````sum_nofoeders= CALCULATE (
SUM ( ORDER_HEADER[nofOrders] ),
)``````

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

Helper I

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

If somebody is interested, I'm willing to share a PBIX on this.

Hans
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Helper I

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.