** Conditional formatting of measures values in matrix based on given targets for given values (these values are rows in matrix)

The business users used Weighted Forecast Accuracy – WFA (A Percentage Measure) metric for various business divisions. They wanted to understand each division’s targets better to achieve 100% of the forecast.

We worked on the requirement with a matrix representation having measure values for some divisions(rows) with their targets and different months (as columns), and we must do conditional formatting (color) based on given targets for given divisions(rows) based on their individual targets. The data sets for targets and divisions that will be used to color the measures are as follows:

Note: Format target fields as percentages

Conditions for Coloring:

If the Measure is Greater than 100%, then Green(#49ed24)

If the Measure is between 50% and 100%, then Yellow(#ede324)

If the Measure is below 50%, then Red(#eb684d)

In the PowerBI dashboard, we displayed the data of a measure in percentage form, where we included values with different divisions (from the given rows) on a per-month basis for the current year. Every division has specified targets; to check whether each division has achieved its targets in different months, we are making it in a colored pattern.

If Division=Household goods have achieved a 100% target of the forecast, then it would be green for them if they achieved 50% or more, yellow and less than 50%, then red.

The same goes for different divisions with different targets of 50%,100%, etc.

Solution:

We created a table with targets as per the given divisions (rows).

So, to achieve this, first, we must have the table with the given dataset. Once it is done, we create the measure as per the requirement that we want to see, giving the output as a color.

If a measure for Division=Daily Use has achieved a 100% target, or it can achieve it, or is more than the required target, then the color output will be green.

Else if it’s less than 100% and greater than 50%, then yellow; it selects red.

Note: We will need to format as per the current year that we have in our table.

Then we created a measure that can be used to format in measure with conditions; here is the stepwise approach:

1^st Step: Create a Measure with the below code, which needs to be modified as per your table/column name:

Once this is created, be sure to have it as datatype Text, else it won’t appear when we choose the conditional formatting field by value option.

2^nd Step: Right-click on the Values tab of the matrix and choose the Conditional Formatting option.

3^rd Step: Choose the Field by value option from the drop-down and choose the above “create measure”.

RESULT

Example for Matrix before conditional formatting

Example for Matrix after conditional formatting