Forum Discussion
Switch Measure - conditional formatting
Hi,
I have several switch-measures in a matrix table, like this example here:
cn4422 ,
Create a Measure for Conditional Formatting: You need to create a measure that will be used for the conditional formatting. This measure should return a value that can be used to determine the color gradient.
ConditionalFormattingMeasure =
SWITCH(
TRUE(),
SELECTEDVALUE(Countries[Country]) = "AT", DIVIDE([G AT], [L AT], 0),
SELECTEDVALUE(Countries[Country]) = "BE", DIVIDE([G BE], [L BE], 0),
SELECTEDVALUE(Countries[Country]) = "CH", DIVIDE([G CH], [L CH], 0)
)
Apply Conditional Formatting: Once you have the measure, you can apply conditional formatting to the matrix table.Go to the matrix visual in your Power BI report.
Click on the dropdown arrow next to the measure you want to format.
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
In the conditional formatting dialog, choose "Field value" and select the ConditionalFormattingMeasure you created.
Set the color scale to a three-color gradient and configure the minimum, midpoint, and maximum values as needed.
3 Replies
cn4422 ,
Create a Measure for Conditional Formatting: You need to create a measure that will be used for the conditional formatting. This measure should return a value that can be used to determine the color gradient.
ConditionalFormattingMeasure =
SWITCH(
TRUE(),
SELECTEDVALUE(Countries[Country]) = "AT", DIVIDE([G AT], [L AT], 0),
SELECTEDVALUE(Countries[Country]) = "BE", DIVIDE([G BE], [L BE], 0),
SELECTEDVALUE(Countries[Country]) = "CH", DIVIDE([G CH], [L CH], 0)
)
Apply Conditional Formatting: Once you have the measure, you can apply conditional formatting to the matrix table.Go to the matrix visual in your Power BI report.
Click on the dropdown arrow next to the measure you want to format.
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
In the conditional formatting dialog, choose "Field value" and select the ConditionalFormattingMeasure you created.
Set the color scale to a three-color gradient and configure the minimum, midpoint, and maximum values as needed.Hi bhanu_gautam,
thanks for your reply and the provided measure and explanation!
The conditional formatting itself works fine.
However, it considers all rows from all countries, using the highest and lowest values across all countries as reference points.
What I would like instead is for each row to reference only itself. In this case, we have only two rows—so for AT, the lowest value would be €47.19, and the highest would be €55.58, regardless of the other rows.
Is there a way to apply conditional formatting in this way?
cn4422 , Not sure but trying separate measure than and use it
reate Individual Measures for Each Country: Create separate measures for each country that calculate the ratio or value you want to use for conditional formatting.
G_AT_L_AT_Ratio = DIVIDE([G AT], [L AT], 0)
G_BE_L_BE_Ratio = DIVIDE([G BE], [L BE], 0)
G_CH_L_CH_Ratio = DIVIDE([G CH], [L CH], 0)
Apply Conditional Formatting: Apply conditional formatting to each measure separately in the matrix visual.Go to the matrix visual in your Power BI report.
Click on the dropdown arrow next to the measure you want to format (e.g., G_AT_L_AT_Ratio).
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
In the conditional formatting dialog, choose "Field value" and select the corresponding measure (e.g., G_AT_L_AT_Ratio).
Set the color scale to a three-color gradient and configure the minimum, midpoint, and
