ROUND Function in Power BI – Conditional Formatting

ROUND Function in Power BI – Conditional Formatting

No comments

In this tutorial, I’ll discuss how to use the ROUND function in Power BI when it comes to conditional formatting. Excel users might be familiar with how this function works in Excel, and we actually use it very similarly in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

I recently encountered a problem with conditional formatting, and I’ll show how I solved it using this function. I needed to highlight a number in a certain column if the results from the other 2 columns didn’t match. After talking to one of our Enterprise DNA experts, I figured out that I just needed to use a DAX function called ROUND

ROUND Function In Power BI

The ROUND function is used to round a number to the specified number of digits. You can check the Microsoft documentation for this function here.

ROUND Function In Power BI

The number term refers to the number that you want to round. In my case, this is the measure. On the other hand, the num_digits represents the number of digits from the decimal point that you want to round.

ROUND Function In Power BI

You can also check these considerations when using the ROUND function.

ROUND Function In Power BI

Let’s discuss how I used this DAX function in a calculation and conditional highlighting that I did for a client.

Margin Calculation Without ROUND Function In Power BI

Here is the situation that I had when I did the Margin Target calculation and conditional highlighting for my client. I broke down this table by job. I also added a Job Count column so we could see the total amount of jobs.

ROUND Function In Power BI Sample Table Scenario

In this scenario, I used a measure that I named as Margin Target Test to get the results for the Info Page Margin column.

Margin Target Test measure

The Info Page Margin column is a margin calculation. Typically, the margin is in a number form. So, I used this measure to divide the margin to 100 in order to get the percentage.

Incorrect Conditional Formatting Sample Scenario

In the original measure, I was trying to highlight the number in the Info Page Margin column to orange, if it doesn’t match the actual margin which is the number in the Margin% v2 column.

ROUND Function In Power BI Sample Table Scenario

In order to show you that, here’s a tab that I labeled as incorrect. This will show you the highlighted incorrect values.

ROUND Function In Power BI Sample Table Scenario

As you can see, the margin from the Info Page Margin column is 37.5%. Then, the numbers in the Margin% v2 and TESTING columns matched. In that case, the 37.5% shouldn’t be highlighted. This table is showing an incorrect output because of the original way that I’ve set up the measure.

Reviewing The Incorrect Measure

Here’s the incorrect measure that I used for the previous scenario.

sample margin calculation in Power BI

In this measure, I created a variable called MarginNoGood. This variable contains a condition where if the result from the TESTING column doesn’t match the number from the Margin% v2 column, then the value will be set to 1. If not, then the value will be set to 0.

sample margin calculation

Then, I created the CompletedMargin variable. I used this to calculate the number of jobs that were under the “Job Completed” status and those that resulted in 0 from the MarginNoGood calculation.

sample margin calculation

After that, I used the RETURN keyword wherein I could get a 6 or a 0 that I can use to conditionally highlight the background of the number under the Info Page Margin column.

sample margin calculation

Creating A Conditional Highlight Background

I created a conditional highlight background by opening the Info Page Margin here. Just hover over the Conditional formatting option and click the Background color option.

Conditional Formatting in Power BI

Then, there will be different options here. In this example, I used the Rules options.

Conditional Formatting in Power BI

From there, I set a rule wherein if the value is 6, then that result from the Info Page Margin column should be highlighted with an orange background.

Conditional Formatting in Power BI

With the previously mentioned calculation and conditional formatting setup, the numbers under the Info Page Margin column were highlighted incorrectly. As you can see, the numbers under Margin% v2 and TESTING columns matched but the numbers in the Info Page Margin column were still highlighted.

ROUND Function In Power BI Sample Table Scenario

So, I had to use the ROUND function in order for it to work correctly.

Conditional Formatting Using ROUND Function In Power BI

I created another measure that I named as Job Info Margin. The formula that I used in this measure is almost similar to the previous one. However, I used the ROUND function in this formula. I also used 3 as my num_digits. That means I want to round it to 3 decimal places.

ROUND Function In Power BI Sample Table Scenario

By doing that, all the numbers in the Info Page Margin column are now highlighted in orange. That’s because the numbers in the Margin% v2 and TESTING column didn’t match.

As I scroll down the table, I can see that there are rows where the Margin%v2 and TESTING column matched. Therefore, it didn’t highlight the numbers under the Info Page Margin.

And that is the correct output that I need. Hence, with the help of the ROUND function, my conditional highlighting is now working correctly.

Conclusion

On a final note, the ROUND function in Power BI is definitely valuable when it comes to conditional formatting. For those who are familiar with Excel, you’ve probably had some experience at some point using the ROUND function. But here in DAX, if you ever encounter an instance where you’re stuck with analyzing why two numbers or percentages don’t match, try using this function.

I hope this helps you in your future DAX endeavors.

Check out the links below for more examples and related content.

Thank you!

Jarrett

Membership Banne

***** Related Links *****
Calculate Percentage Margin In Power BI Using DAX
Custom Conditional Formatting Techniques In Power BI
Showcase Unique Insights Using Conditional Formatting In Power BI

***** Related Course Modules *****
Data Visualization Tips
Mastering DAX Calculations
Business Analytics Series

***** Related Support Forum Posts *****
Conditional Formatting For Not-equal To A Summary Value
Create A Table To Count The Color In A Conditional Formatting
Conditional Format Negative Percentages
For more conditional formatting support queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.