cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robsukaldi
Frequent Visitor

conditional formatting based on dynamic value from slicer

Hello everyone,

 

I'm trying to use conditional formatting to show feasible investments given a varying budget. depending on the region my budget differs as well as the investment costs, see the table below. In my dashboard I have a slicer to show those mentioned investment costs and budget. I can't figure out how to highlight the feasible investments. Any help would be greatly appreciated, I'm not familiar with the DAX editor, but happy to try it if necessary.

 

For instance in region 1 I would like to highlight investment 1 and 2.

 

Region Budget  Investment 1  Investment 2  Investment 3  Investment 4  Investment 5  Investment 6  Investment 7 
1             1.768             1.411             1.282             3.718             3.868             4.283             3.751             2.978
2             1.334             1.387             1.067             2.661             2.611             3.794             2.957             2.908
3             1.441             1.399             1.122             2.779             2.781             3.832             3.312             2.927
4             2.069             2.114             1.874             5.612             5.620             6.249             4.531             3.629
5             2.412             2.923             2.506             6.561             5.075             5.584             4.532             3.507
1 ACCEPTED SOLUTION
DataZoe
Super User II
Super User II

@robsukaldi I am not sure how your data is set up but I think you could this with a couple measures. I am assuming your have separate columns for each investment and the budget, like the table above. And that feasible investments are under the budget.

DataZoe_0-1618930790712.png

To make this a little easier I would actually unpivot your data in Power Query first:
Right-Click on Region --> Unpivot Other Columns. I also right clicked on Attribute and then Transform --> Clean and Transform --> Trim.

DataZoe_1-1618930843685.png

 

Then I set up an Investments measure and Budget measure:

 

Investment = sum('Table Unpivot'[Value])
 
Investment Budget = CALCULATE([Investment],'Table Unpivot'[Attribute]="Budget")
 
and the measure to control the conditional formatting, you can pick any color here!:
 
Feasible Investment Color =
if([Investment]<[Investment Budget],"#FABEFA",blank())
 
Now, in the matrix, you can go to Formatting --> Conditional Formatting and toggle Background color. Change this to Field Value and use the Feasible Investments Color measure above:
DataZoe_2-1618931109355.pngDataZoe_3-1618931135381.png

 

Hope this helps!

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
DataZoe
Super User II
Super User II

@robsukaldi I am not sure how your data is set up but I think you could this with a couple measures. I am assuming your have separate columns for each investment and the budget, like the table above. And that feasible investments are under the budget.

DataZoe_0-1618930790712.png

To make this a little easier I would actually unpivot your data in Power Query first:
Right-Click on Region --> Unpivot Other Columns. I also right clicked on Attribute and then Transform --> Clean and Transform --> Trim.

DataZoe_1-1618930843685.png

 

Then I set up an Investments measure and Budget measure:

 

Investment = sum('Table Unpivot'[Value])
 
Investment Budget = CALCULATE([Investment],'Table Unpivot'[Attribute]="Budget")
 
and the measure to control the conditional formatting, you can pick any color here!:
 
Feasible Investment Color =
if([Investment]<[Investment Budget],"#FABEFA",blank())
 
Now, in the matrix, you can go to Formatting --> Conditional Formatting and toggle Background color. Change this to Field Value and use the Feasible Investments Color measure above:
DataZoe_2-1618931109355.pngDataZoe_3-1618931135381.png

 

Hope this helps!

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

Thank you very much! Took me some time to get it work, but I'm really thankful. I do have a follow up question. I've a slicer from a different which I would like to use for the formatted table. Do you know whether that's possible?

@robsukaldi Of course, you can create a relationship between the tables and it should work. can you share the table structure?

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

I'm a bit reluctant to share my whole dataset due to confidential info, but I hope this suffices. I made a similar structure as a would do in my 'real' dataset. the underlying data and pbix file can be downloaded via this wetransfer link (couldn't figure out how to share it like you did...).

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors