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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Employee
Employee

@Anonymous 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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
DataZoe
Employee
Employee

@Anonymous 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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

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?

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.