Forum Discussion
Traffic light KPI with an threshold input
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.
- 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?
- Are there Best-Practice formulas or just the typical if, else, select?
- 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
8 Replies
- MFelix
Super User
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. 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.
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
