Forum Discussion
Gauge visual conditional formatting
Hi, I have a table with
1. 'target deadline' which is the overall financial year target i.e., 500 for the 4 quarters
2. 'Target' for each quarter i.e., 125 every quarter
3. 'Metric value' for each quarter that they report on i.e., 100 which means they did not meet the target of 125 this quarter
4. 'Variance threshold' which shows the value that will be amber (119 so anything between 119 and 124 will be amber colour)
4. 'RAG' to show if they met target or not (red, amber, green)
I want to show this on a gauge so it shows an overall gauge with the RAG through conditional formatting, but also when the filter 'quarter' is selected, this will also pick up the RAG for that quarter.
daniellamoore29 Try this. PBIX is attached below signature.
Measure = VAR __Target = SUM('Table'[Target]) VAR __Metric = SUM('Table'[Metric Value]) VAR __Variance = SUM('Table'[Variance Threshold]) VAR __Result = SWITCH( TRUE(), __Metric < __Variance, "Red", __Metric < __Target, "Yellow", "Green" ) RETURN __Result
5 Replies
daniellamoore29 Try this. PBIX is attached below signature.
Measure = VAR __Target = SUM('Table'[Target]) VAR __Metric = SUM('Table'[Metric Value]) VAR __Variance = SUM('Table'[Variance Threshold]) VAR __Result = SWITCH( TRUE(), __Metric < __Variance, "Red", __Metric < __Target, "Yellow", "Green" ) RETURN __ResultI cant seem to select 'Measure 2' for colour conditional formatting.
Greg_Deckler how can I add the measure to the field value conditional formatting please? I am unable to change the meausre to text data type, it doesn't give me the option which other forums have said resolves the issue?
daniellamoore29 This is what I did in the PBIX file (below). Not sure what issue you are having. If you want to use hex codes, you need to make sure your measure returns those as text, like "#FF0000" I believe.
Measure = VAR __Target = SUM('Table'[Target]) VAR __Metric = SUM('Table'[Metric Value]) VAR __Variance = SUM('Table'[Variance Threshold]) VAR __Result = SWITCH( TRUE(), __Metric < __Variance, "#FF0000", __Metric < __Target, "Yellow", "#00FF00" ) RETURN __Result
Greg_Deckler I've managed to add the Meaure to the conditional formatting as I amended the data types in the backend, thank you.
I still have one issue, some quarters show the correct colour i.e., green colour or red colour, however some quarters still show blue. How do I amend these colours also?
