Conditional Formatting in Power BI using measures.

[Manav-Shah]

I need to implement the same conditional formatting in Power BI using a measure that apper in below excel sheet.

I have two data sheets in Excel.

1) Dashboard

2) Data for conditional formatting 

I need to apply conditional formatting to the data in the dashboard sheet in Power BI based on the following condition:

Condition: For each individual month's value, it should be compared to the total value for the same problem in the conditional formatting sheet. If the month's value is greater than the total value, the font should appear in RED color.

[Dangar332]

hi, @Manav-Shah 

try below measure

Measure = 
var a = CALCULATE(SUM('Table (2)'[value]),'Table (2)'[id]=SELECTEDVALUE('Table'[id]))
var b = IF(a=BLANK(),CALCULATE(SUM('Table'[Value]),'Table'[id]=MIN('Table'[id]),ALL('Table'[Attribute])),a)
var c= IF(SUM('Table'[Value])>=b ,"red","#000000")
RETURN
c

1. initial data look like below

2. transform it to power query and apply unpivot column

3. close and apply

4. crate matrix which look like below

5. apply conditional formatting using above measure

[Anonymous]

Hi @Manav-Shah 

You can try the following.

Relationship:

1. Create a calculated column

Column = 
IF (
    Dashboard[Research Problem] = RELATED ( 'conditional formatting'[Problem] ),
    IF ( 'Dashboard'[Value] >= RELATED ( 'conditional formatting'[Total] ), 1, 0 ),
    0
)

2. Follow the settings shown in the screenshot below

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

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

[Manav-Shah]

Hi @Anonymous ,

Can you explain how you calculated 'Dashboard[Value]' in power bi?

[Anonymous]

Hi @Manav-Shah 

’Dashboard[Value]‘ field are the raw data from the sample created based on the table you provided. 

Sample:

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Community Support Team _Yuliax

[Manav-Shah]

Hi @Anonymous ,

Sorry, I forgot to mention my main data format.

I have data in below format and I have created formulate table and then apply conditional formatting, so I have to do same thing in Power BI

[Anonymous]

Hi @Manav-Shah 

I would like to apologize for the belated reply.

If your problem is not solved yet, can you tell me how month value is obtained?

Best Regards,
Yulia Xu

[Manav-Shah]

I have data in below format 

then I used countifs funcrtion for calculating all months total.

For conditional formatting, I intend to set conditions based on the average of the last three months multiplied by 140%. For instance, when applying conditional formatting for January, it is computed by taking the average of October, November, and December and multiplying it by 140%. Similarly, for February, it is calculated using the averages of November, December, and January, then multiplied by 140% and so on for other months.

[talespin]

Hi @Manav-Shah ,

You create a measure and set Formatting > cell > font

Measure =

//This is your Matrix/Dashboard

VAR _Val = SELECTEDVALUE(Table[Research Problem])

//This is for Total table, replace table/column names/Filter value

VAR _Val2 =  CALCULATE( VALUES(Table[Total]), Table[Problem] = _Val  )

RETURN IF( _Val > _Val2, "Red")

[Manav-Shah]

I do not want conditional formatting using max value.

I need Conditional formatting from below image in Power BI.

[talespin]

hi @Manav-Shah ,

It is not takeing MAX value, it is taking MAX of single value, I changed it to VALUES.

Measure =

//This variable fetches the current value of Research problem in current cell of your Matrix/Dashboard

VAR _Val = SELECTEDVALUE(Table[Research Problem])

//This is for Total table, it searches your total table for Problem text it retrieved into above variable .Replace table/column names/Filter value. Since there is only one value for one problem, MAX or VALUES doesn't make any difference.

VAR _Val2 =  CALCULATE( VALUES(Table[Total]), Table[Problem] = _Val  )

RETURN IF( _Val > _Val2, "Red")

[Manav-Shah]

Hi @talespin,

It shows error.

Can you do conditional formatting using formula that I have written in last image?

[talespin]

Hi @Manav-Shah 

Please check response from @Anonymous . That is what you need.