Conditional formatting on column, shown as percentage of total

[Anonymous]

Goodmorning Power BI experts,

Yesterday I wanted to add conditional formatting on a column in my table. Normally this wouldn't be any problem, but this scenario is a bit different compared to others. I have 2 columns in this Power BI dashboard, 1 is for "hours worked" and the other one is "% billable". Technically those are the same values, but the second value is shown an "percentage as total of column". 

We have 4 employees so that is why there are 4 columns.

We do have a KPI target of x% per week billable, which I want to show using different colors. However, if I want to add conditional formatting it only applies to the value of the hours worked. 

Is there any way to add conditional formatting to the second column, % billable?

[amitchandak]

@Anonymous , Not very clear, is it Table or Matrix?

In both cases, you can have conditional formatting on a specific filed/value.

If need create a color measure and used that in conditional formatting

example

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")

Color Year = if(FIRSTNONBLANK('Table'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous]

@amitchandak Thank you for your fast reply. Sorry, it is a matrix.

I dont think the option is to make a 'static' measure, since employees can be free a few days a week. For example, the target is 50%. If an employee is free for 4 days, they will never be able to get their target of 50%. This is because the measure tells them they have to make 20 hours, instead of 50% of the hours have to be billable if that makes any sense. 

To clearify, (for example) 50% of the hours made have to be billable. This means that in a 'normal' week they have to have at least 20 billable hours. But some weeks the value can be changed to 8 hours, because they are free or sick 3 days that week.

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Goodmorning @Anonymous ,

A new dashboard with some dummy data has been created. I hope this clarifies the situation a bit more. It is downloadable here . If you have any solutions or comments I would love to know.

As shown in the dashboard, I would like to conditionally format the "% column" in the table. This has to be something like, "IF % > 50% THEN COLOR=GREEN, ELSE COLOR=RED".  

Our data is a bit more complicated, since we have multiple 'hours worked' records per day, per employee. This makes it complicated, since we do not have 1 value per employee per day.

Kind regards,

Wouter

[Anonymous · Accepted solution]

HI @Anonymous,

For your scenario, you can extract the current category field value and use them as parameters to lookup/calculate corresponding values from the second column, then you can use this result as a condition for color formating.

BTW, I can't view the file from the shared link, can you please paste some dummy data here in table format? 

Regards,

Xiaoxin Sheng