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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Conditional formatting when comparing yesterdays date to a 6 week average

I have two score cards in the image below. The top one is Unique Visitors showing yesterdays data while the bottom one is the daily average of the last 6 weeks. 

 

What approach can I take if I want the top scorecard to either be green if it is higher than the 6 week daily average or red if it is lower than the 6 week average?

 

image.png

The image below is the filter I use for the 6 week daily average metric. I have a separate 'date' table that has a 'period' field that allows me to select yesterday, 2 weeks ago, 6 weeks ago, etc. 

 
 

image.png

I'm using the following tables, just for reference:

Site Totals Table:

- Metric: Unique Visitors -> used on the top scorecard and that scorecard has a filter on it to only look at yesterdays data.

- Calculated metric for the  6 week daily average scorecard: Avg Daily UVs = AVERAGEX( VALUES(Date_and_Periods[Date]), ('Site Totals'[Total UVs]))

 

Appreciate any help!

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I create three simple measures to test.

Top card value = 1200
Avg Daily UVs = 1300
Visual control = 
IF(
    [Top card value]>[Avg Daily UVs],
    "green",
    "red"
)

 

Then you may make the top value card selected, go to 'Visualization' ribbon, click 'Format'=>'Background'=>'Conditional formatting', set as below.

e1.png

e2.png

 

Result:

e3.png

 

You may also try the Card with States Custom Visual from the marketplace.

 

Best Regards

Allan

 

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

View solution in original post

1 REPLY 1
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I create three simple measures to test.

Top card value = 1200
Avg Daily UVs = 1300
Visual control = 
IF(
    [Top card value]>[Avg Daily UVs],
    "green",
    "red"
)

 

Then you may make the top value card selected, go to 'Visualization' ribbon, click 'Format'=>'Background'=>'Conditional formatting', set as below.

e1.png

e2.png

 

Result:

e3.png

 

You may also try the Card with States Custom Visual from the marketplace.

 

Best Regards

Allan

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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