Conditional format on matrix table

[Cheng2202]

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").

 

[v-jianpeng-msft]

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.

[Cheng2202]

@johnbasha33 @v-jianpeng-msft 

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? 😊

[v-jianpeng-msft]

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.

[Cheng2202]

@v-jianpeng-msft 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? 🤔

[Cheng2202]

@v-jianpeng-msft Is it possible to remove where it says yellow? 🤔

[v-jianpeng-msft]

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.

[johnbasha33]

@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 !!

[Cheng2202]

@johnbasha33 Replied to wrong message 😅: 

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? 😊