topic Re: Conditional format on matrix table in Desktop

Conditional format on matrix table

Cheng2202 — Thu, 11 Apr 2024 13:34:53 GMT

I want to make an icon conditional format on a matrix table but I have som issues. I can just make conditional format based on one column but I want it to compare with another one. I also just want the icons just on "Totalt" and not the sum of "Budsjett". I want the icons to come as follows:

Red: When "Totalt" is 0. Yellow: When "Totalt" is between 0-10% of "Budsjett". Green: When "Totalt" is over 10% of "Budsjett" Is this possible? How?

Picture 1: The matrix table.

Picture 2: How it is set up.

Picture 3: The measure on value ("Verdier").

Re: Conditional format on matrix table

johnbasha33 — Thu, 11 Apr 2024 15:46:05 GMT

Cheng2202

To achieve conditional formatting based on multiple conditions and comparison between two columns in a matrix table in Power BI, you can follow these steps:

1. **Create Conditional Formatting Measure**:
Create a measure that calculates the color based on your conditions. You can use DAX expressions to evaluate the conditions and return the appropriate color code. Here's an example measure:

```DAX
Conditional Format =
VAR Totalt = SELECTEDVALUE('YourTable'[Totalt])
VAR Budsjett = SELECTEDVALUE('YourTable'[Budsjett])
VAR PercentOfBudsjett = Totalt / Budsjett
RETURN
SWITCH(
TRUE(),
Totalt = 0, "Red",
AND(PercentOfBudsjett > 0, PercentOfBudsjett <= 0.1), "Yellow",
PercentOfBudsjett > 0.1, "Green",
BLANK()
)
```

Replace `'YourTable'`, `'Totalt'`, and `'Budsjett'` with your actual table name and column names.

2. **Apply Conditional Formatting**:
Go to the conditional formatting settings for the "Totalt" column in your matrix table. Choose "Icon Sets" as the formatting type. Then, use the measure you created above as the field to base the icons on.

3. **Configure Icon Rules**:
Set up three icon rules corresponding to your conditions:
- Red: Rule where the measure equals "Red".
- Yellow: Rule where the measure equals "Yellow".
- Green: Rule where the measure equals "Green".

4. **Ensure Icons Only on "Totalt"**:
Make sure the conditional formatting is applied only to the "Totalt" values and not the "Budsjett" values. You can adjust the conditional formatting settings to target only the "Totalt" column.

By following these steps, you should be able to apply conditional formatting with icons based on multiple conditions and comparison between two columns in your matrix table in Power BI. Adjust the measure and rules as needed to fit your specific requirements.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Conditional format on matrix table

Anonymous — Mon, 22 Apr 2024 06:54:18 GMT

Your solution is great, johnbasha33 . It worked like a charm!

Hi, Cheng2202

Have you already solved the current problem? I tried the workaround of johnbasha33 using the following sample data and it worked

I have the following table visual, and then I create one measure using the following DAX expression:

icon color = SWITCH(TRUE(), [Profit]*0.1>=[Input],"green", [Profit]>0&&[Profit]*0.1<[Input],"yellow", "red")

Set the conditional formatting as follows:

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Conditional format on matrix table

Cheng2202 — Mon, 29 Apr 2024 11:33:50 GMT

johnbasha33 Anonymous

Thanks for your help and sorry for late reply! Had much to do lately.

I encountered a problem with the measure. On "SELECTEDVALUE('YourTable'[Totalt])" and "SELECTEDVALUE('YourTable'[Budsjett])" I can't fill in as those numbers are from another measure and not in the table. The measure for..

..Totalt is:

..Budsjett is:

Do you have any solution for this? 😊

Re: Conditional format on matrix table

Anonymous — Tue, 30 Apr 2024 00:43:30 GMT

Hi, Cheng2202

Thank you very much for your reply. If you're two measures, then your DAX expression looks like this:

icon color = SWITCH(TRUE(), [Totalt]>[Budsjett]*0.1,"green", [Totalt]>0&&[Totalt]<=[Budsjett]*0.1,"yellow", "red")

Then you need to put these three measures in the matrix table and conditionally format the icon color.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Conditional format on matrix table

Cheng2202 — Thu, 02 May 2024 11:32:55 GMT

Anonymous Is it possible to remove where it says yellow? 🤔

Re: Conditional format on matrix table

Cheng2202 — Thu, 02 May 2024 11:33:11 GMT

johnbasha33 Replied to wrong message 😅:

Thanks for your help and sorry for late reply! Had much to do lately.

..Totalt is:

..Budsjett is:

Do you have any solution for this? 😊

Re: Conditional format on matrix table

Cheng2202 — Thu, 02 May 2024 11:32:39 GMT

Anonymous And I also wondered how can I make it work on a matrix table and not a normal table as in your example:

In a normal table I can't get the right lines after first column and fat text in the last column:

Are there any solution for that? 🤔

Re: Conditional format on matrix table

Anonymous — Fri, 03 May 2024 06:59:14 GMT

Hi Cheng2202

Thank you for your reply, you can remove the yellow from the totals row. You need to use the ISINSCOPE or HASONEVALUE function to determine whether the current row is a total row.

HASONEVALUE function (DAX) - DAX | Microsoft Learn

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Here's a reference for using them to remove totals:

Just change the DAX expression in the image to:

icon color = IF( ISINSCOPE('Table'[Product]), SWITCH(TRUE(), [sales amount]>=300,"green", [sales amount]>=200,"yellow", "red" ) )

Here's what it looks like:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.