Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Manav-Shah
Helper I
Helper I

Conditional Formatting in Power BI using measures.

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

CF.png

 

 

2) Data for conditional formatting 

 

 

CF1.png

 

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.

 

 

12 REPLIES 12
Dangar332
Super User
Super User

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

Dangar332_0-1707288886322.png

 

2. transform it to power query and apply unpivot column

Dangar332_1-1707289008389.png

3. close and apply


4. crate matrix which look like below

5. apply conditional formatting using above measure

Dangar332_3-1707289128061.png

 

 

 

v-xuxinyi-msft
Community Support
Community Support

Hi @Manav-Shah 

 

You can try the following.

 

Relationship:

vxuxinyimsft_0-1707288516689.png

 

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

vxuxinyimsft_0-1707287616116.png

 

vxuxinyimsft_1-1707287653752.png

 

vxuxinyimsft_2-1707287723646.png

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.

Hi @v-xuxinyi-msft ,

 

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

Hi @Manav-Shah 

 

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

 

Sample:

vxuxinyimsft_0-1707289896720.png

 

vxuxinyimsft_1-1707289954335.png

 

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

Hi @v-xuxinyi-msft ,

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

 

ManavShah_0-1707295755648.png

 

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?

vxuxinyimsft_0-1709175076639.png

 

Best Regards,
Yulia Xu

I have data in below format 

 

ManavShah_0-1709185042835.png

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
Solution Sage
Solution Sage

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

I do not want conditional formatting using max value.

 

I need Conditional formatting from below image in Power BI.

 

ManavShah_0-1707218155669.png

 

 

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 =  CALCULATEVALUES(Table[Total]), Table[Problem] = _Val  )
RETURN IF( _Val > _Val2, "Red")

Hi @talespin,

It shows error.

 

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

Hi @Manav-Shah 

 

Please check response from @v-xuxinyi-msft . That is what you need.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.