cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional Formatting Using Custom Measure

Power BI already gives us a way of applying conditional formatting on the table and matrix visuals using the default settings.

Suppose I need to give some custom color-based formatting to my cells in a table visual, how will I achieve this?

There is an easy way to create custom color formattings in Power BI using a simple measure calculation. In this article I will walk you through a step-by-step example on how to implement this in Power BI.

 

Let us consider the following table visual:

Pragati11_1-1601307787231.png

I have got sales by clothing category, by day of a week in the above table visual.

 

Now, my task is to give a custom conditional formatting to the Day of Week column above based on the Clothing Category.

For example - Clothing Category = Jackets should be GREEN

                       Clothing Category = Jeans should be BLUE

                       Clothing Category = Pants should be AMBER

                       Clothing Category = Shirts should be RED

 

In order to give a custom color coding, I will create a simple DAX measure to achieve this:

cus1.png

The above DAX is a simple SWITCH statement, that gives a custom color based on the clothing categories. 

Colors are represented using COLOR HEX CODES.

 

Now, let us see how we can use this custom measure to give our table a conditional formatting.

cus2.png

 

Click on the table visual --> go to Formatting options --> Conditional Formatting --> Background Color --> ON

 

Once you do this a new window appears with default background color options.

Now we need to modify this setting to use the custom measure that we created to get the background color working as we want. See below:

cus3.png

Now in the above window, I have selected following options:

  1. Format By - Field Value option is selected because I want to add conditional value based on the custom measure created.
  2. Based on Field - This section takes the name of the field which you want to use for the conditional formatting. In our case it is Day Color so we choose that.
  3. Apply To - Here you need to mention where you want to apply this conditional formatting. You will see options: Values Only, Values and Totals, Totals Only. I am choosing Values Only for this example. Modify it as per your requirement.

Once you click OK in the above window, you end up with following:

cus4.png

 

Voila! We have given conditional formatting to Day of Week column based on the clothing Category value.

I just tried to add a simple legend on the top to represent the color coding.

 

So, this is how one can use a custom color formatting in Power BI by creating a simple measure for it.

 

Hope this article helps everyone out there.

 

- Pragati