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
sy898661
Helper V
Helper V

Overall Average % based on what month it is

Hi all!

 

I have a question that is puzzling me.

 

So I have data that looks like the following:

 

Capture.PNG

 

And I have created a column chart visual that has WIP Completion Month in the x axis and Average % in the y axis.

What I am trying to do is conditionally format the columns so that if the overall average each month is >= 95%, the column will be green, and if it is below 95% it will be yellow.

 

So is there a way to write a code/new column that says:

If the overall average % of X month is >= 95%, "#32a852", if it is below 95%, "#e4eb60" 

(random green/yellow color HEX's I found, to then use conditional formatting Field Value setting)

 

I tried using the conditional formatting tool and setting Rules, but since Yield % can sometimes be higher than 100% the rules are not working the way I would like.

 

I'm hoping there is a simple solution that I just am not thinking of, so any help will help!! 


Thank you!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yes, its not too hard.  Here is a simple example.

Step 1:  Create a measure that calculate the average.  I.e.

Average Percent = AVERAGE('YourTable'[Yield Variance % to Plan])

 

Step 2:  Create a measure that checks the result and provides the hex value based on the answer.  Use a switch statement if you need more than 2 outcomes.  Here is an example

Color Percent = VAR result = [Average Percent]
VAR output = IF(
	result >= 0.95,
	"#32a852", 
	"#e4eb60"
)
RETURN
output

 

Step 3:  Place the Color Percent into your conditional formatting and then make sure your "month" is part of your visual axis.  This will ensure the monthly context is passed into the measure.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Yes, its not too hard.  Here is a simple example.

Step 1:  Create a measure that calculate the average.  I.e.

Average Percent = AVERAGE('YourTable'[Yield Variance % to Plan])

 

Step 2:  Create a measure that checks the result and provides the hex value based on the answer.  Use a switch statement if you need more than 2 outcomes.  Here is an example

Color Percent = VAR result = [Average Percent]
VAR output = IF(
	result >= 0.95,
	"#32a852", 
	"#e4eb60"
)
RETURN
output

 

Step 3:  Place the Color Percent into your conditional formatting and then make sure your "month" is part of your visual axis.  This will ensure the monthly context is passed into the measure.

Thank you so much @Anonymous !! That worked perfectly!!!! 🙂

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.