Traffic light KPI with an threshold input

[Anonymous]

Dear community and P-BI specialists,

I am trying to create a traffic light indicator for some data.

The concerned Excel file contains several columns with data. I want to associate the values of each columne to a range of a traffice light (red, yellow or green) , according to its respective threshold.

1. Do I calculate these color-ranges in new seperated columns (One for each value and color, which makes three additional columns for each column with actual data, i.e. Ph-Value --> Ph-Value Green, Ph-Value Yellow, Ph-Value Red [containing, 0 or 1])), or should I calculate these ranges in only one new column (Ph-Value --> Ph-Value colors [contains, red, yellow or red]) or are Power-Bi measures appropriated for these kind of challenge?
   
   
2. Are there Best-Practice formulas or just the typical if, else, select?
   
   
3. The treshhold Values should be imported externally, because I want it to be adaptable for other users. I'd prefer an input label in P-BI, but I guess it doesn't exist .... Dou you have a proposition to this issue?

Thanks a lot for your help in advance,

Volkmar

[Anonymous]

Hi @MFelix ,

thanks for your Answer, I can work with that.

Why is there a need to disconnect the tables to grab the values?

BR

[MFelix]

You only will need a disconnected table if you want to have single measure for the condittional formatting.

What I mean is that assume you want to have this measure:

Condittional formatting =
SWITCH (
    TRUE ();
    SUM ( Table[ABC] ) < 1000; "Red";
    SUM ( Table[ABC] ) >= 1000
        && SUM ( Table[ABC] ) >= 2000; "Yellow";
    SUM ( Table[ABC] ) < 2000; "Green";
   SUM ( Table[ZYX] ) < 1000; "Red";
    SUM ( Table[ZYX] ) >= 1000
        && SUM ( Table[ZYX] ) >= 2000; "Yellow";
    SUM ( Table[ZYX] ) < 2000; "Green"

)

If you use the same table to get the values since you have column ABC and columnZYX in the same table the calculations are picking up the first  column you place on the switch measure in this case column ABC so the result would be to have a line with all values equal. In this case you would need to have the disconnected table making the specification that calculations for column ABC would only pick up the column ABC.

[Anonymous]

Hey Miguel,

thanks again for your fast response.

Since I want to use the outcome of the measures for diagrams (e.g. a bar chart), I wonder if I can use those measures, beacuse PowerBI asks for values. So I can't drop this measures in the field value because they contain words and not values ....

I'd like to exhibit the overall amount of the green, yellow and red values ...

What would you suggest?

BR

[MFelix]

Hi @Anonymous ,

The measure can be used in any condittional formatting, since when making the use of colours it identifies the Red, Green and Yellow and converts it to colour coding.

however if you want you can customize the colour by adding the HEX codes of your choice you just need to replace the "Red" by "#FFFFFF" the codes must always have the # and 6 characters.

[Anonymous]

Hi Miguel,

thanks to your suggestions I was able to create a measure that contains the colors as words.

Unfortunately I can't use it in diagrams, it only allows me to drag & drop the measure in the quickinfo field. Also the conditional formating option doesn't allow to select this measure ....

I also tried to calculate these colors directly in the table as new column. This works fine and it ca be shown in a diagram, because Power BI can count the amount of greens, yellows or reds.

I'd prefer the measure solution, but I can't find the right way to do it. Can you give me any other suggestions?

BR

[MFelix]

Hi @Anonymous ,

What do you mean by:

---

@Anonymous wrote:

Unfortunately I can't use it in diagrams, it only allows me to drag & drop the measure in the quickinfo field. Also the conditional formating option doesn't allow to select this measure ....
....
I'd prefer the measure solution, but I can't find the right way to do it. Can you give me any other suggestions?

You also want to have a count of this values? For that you need a different measure.

Can you share a sample data? and expected result please.

[Anonymous]

Hey Miguel,

I want to evaluate how many values, can be asserted to be red , yellow or green in dependence of the slice selections.

Then these values should be used for diagrams, e.g. a bar stacked bar chart, that exhibist e.g. 10 green, 20 yellow, and 30 red values.

Is it now more clear, what I want?

Tanks & BR

[MFelix]

Hi @Anonymous ,

Believe that this can be solved using a measure in PBI no need for the additional columns and this can be done in a dinamic way something similar to:

Condittional formatting =
SWITCH (
    TRUE ();
    SUM ( Table[column] ) < 1000; "Red";
    SUM ( Table[column] ) >= 1000
        && SUM ( Table[column] ) >= 2000; "Yellow";
    SUM ( Table[column] ) < 2000; "Green"
)

You can have the values to be dinamic based on a table of thresholds but using this type of measure is sufficient, what I believe is a best practice is to create one for each column you want to have condittional formating, otherwise you will also need to had a disconnect table to pick up the columns.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.