Overall Average % based on what month it is

[sy898661]

Hi all!

I have a question that is puzzling me.

So I have data that looks like the following:

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!

[Anonymous · Accepted solution]

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.

[sy898661]

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