topic Re: Conditional Formatting in Power BI using measures. in DAX Commands and Tips

Conditional Formatting in Power BI using measures.

Manav-Shah — Tue, 06 Feb 2024 09:24:01 GMT

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.

Re: Conditional Formatting in Power BI using measures.

talespin — Tue, 06 Feb 2024 12:03:53 GMT

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

Re: Conditional Formatting in Power BI using measures.

Manav-Shah — Tue, 06 Feb 2024 11:18:51 GMT

I do not want conditional formatting using max value.

I need Conditional formatting from below image in Power BI.

Re: Conditional Formatting in Power BI using measures.

talespin — Tue, 06 Feb 2024 12:06:54 GMT

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

Re: Conditional Formatting in Power BI using measures.

Manav-Shah — Wed, 07 Feb 2024 04:26:48 GMT

Hi talespin,

It shows error.

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

Re: Conditional Formatting in Power BI using measures.

Anonymous — Wed, 07 Feb 2024 06:49:24 GMT

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.

Re: Conditional Formatting in Power BI using measures.

Dangar332 — Wed, 07 Feb 2024 06:58:57 GMT

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

Re: Conditional Formatting in Power BI using measures.

Manav-Shah — Wed, 07 Feb 2024 06:59:51 GMT

Hi Anonymous ,

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

Re: Conditional Formatting in Power BI using measures.

Anonymous — Wed, 07 Feb 2024 07:18:44 GMT

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

Re: Conditional Formatting in Power BI using measures.

talespin — Wed, 07 Feb 2024 07:24:19 GMT

Hi Manav-Shah

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

Re: Conditional Formatting in Power BI using measures.

Manav-Shah — Wed, 07 Feb 2024 08:52:52 GMT

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

Re: Conditional Formatting in Power BI using measures.

Anonymous — Thu, 29 Feb 2024 02:52:23 GMT

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

Re: Conditional Formatting in Power BI using measures.

Manav-Shah — Thu, 29 Feb 2024 05:47:00 GMT

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.