Background color for a column having values with different data types in a matrix visualistion

[Anonymous]

Hello,

Looking for some help with the below scenario.

I have a table which contains two different kind of values in a column, one is N/A and other ones have percentage values in it. Because this is under one column, the data type of this is text.

I am using a matrix visualisation which has this column in the Values section. My requirement is to set background color (conditional formatting) for this column, where if the value is greater than 0 and less than 20% those cells should have background color as light green and anything above 20% should have light red and the remaining cells which have N/A should have background color as white.

My problem area:

Because the column data type is text as it as two different type of values, setting background color for it is getting tricky. I did think of putting a placeholder for N/A values as -1%, but in scenarios where we get actual data as -1%, that would get tricky. Given the scenario, N/A cannot be replaced with  0% / a blank value as these values are legit values for other rows.

I know it is probably difficult to get a straight forward solution for columns having different data types, however, if you can suggest any other approach like different placeholders/creating measures that would be really helpful.

Would really appreciate any help/guidance.

Thanks!

[AnalyticsWizard]

@Anonymous 

In Power BI, you can use a measure to convert the text values to numbers for conditional formatting. Here’s how you can do it:

1. Create a Measure: Create a new measure that converts the text values in your column to numbers. For the “N/A” values, you can assign a specific number that is outside the range of your actual data. For example:

Measure = 
VAR Value = SELECTEDVALUE(YourTable[YourColumn])
RETURN
IF(
    Value = "N/A", 
    -1, 
    VALUE(Value)
)

Replace YourTable and YourColumn with the actual name of your table and column.

1. Apply Conditional Formatting: Use this measure for the conditional formatting. You can set the rules such that values between 0 and 0.2 (representing 0% and 20%) have a light green background, values above 0.2 have a light red background, and the value -1 (representing “N/A”) has a white background.

Remember, this approach assumes that your percentage values are stored as text in the format “0.xx”. If they are stored in a different format, you may need to adjust the VALUE(Value) part of the measure accordingly.

I hope this helps! Let me know if you have any other questions. 😊 user Hi Everyone,

I’m tackling a challenge in Power BI and could use some guidance. I am working on line graph and the key (legend) has different components, and I want each one to lead to a different drill-through page when clicked.

Anyone know how to set up independent drill-through for legend items in Power BI? Any tips or tricks would be appreciated.

Thanks in advance! assistant’> In Power BI, setting up independent drill-through for legend items is not directly supported. However, there are a couple of workarounds you can consider:

1. Multiple Drill-Through Pages: Create multiple drill-through pages, one for each value in the legend column. When a user hovers over the line, all the drill-through pages will appear on the tooltip (Modern tooltip). You can guide or instruct your users to click on the appropriate drill-through page1.

2. Multiple Line Charts: If you have only one or two visuals in the drill-through page, you could create multiple line charts for each value in the legend and configure an appropriate tooltip for each line chart1.

3. Sync Slicers: If you have slicers in your report, synchronize them across both visuals so that filtering on one visual will apply to the other2.

4. Cross-Report Drillthrough: If your reports are hosted on Power BI Service, you can sync your reports to Azure Repos and extract the necessary information from there3.

Remember, these are workarounds and might not perfectly fit your needs. 

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
You can base the conditional formatting on the column that is out of the visual. 
So you can add from the PQ column with numeric values like :

Change the data type to "whole number"

Not to put it to you visual, but to use it for conditional formatting rule:

Result:

pbix is attached

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

[Anonymous]

Thank you @Ritaf1983 ! That worked and I learnt a new thing as well 🙂

Appreciate your help!

[Ritaf1983]

Happy to help 🙂