CONDITIONAL FORMATTING FOR YEARS WITH DIFFERENT TARGETS

[Epoloni12]

Hi all, 

I have a simple measure of costs which have different targets for 2022, 2023 and 2024. 

I need that my table replies to conditional formatting considering that, once a specific year is flagged in the filter, the colors will consider the targets of that year. 

What's the calculation for that, please?

Thank you.

[v-yilong-msft]

Hi @Epoloni12 ,

I create a simpe table and a new measure.

MEASURE =
VAR _A =
    MAX ( 'Table'[Year] )
RETURN
    SWITCH ( _A, 2022, 100, 2023, 200, 2024, 150, BLANK () )

Then I create a measure named Color and go to the Conditional formatting.

Color =
IF (
    'Table'[Measure] = 200,
    "Green",
    IF ( 'Table'[Measure] = 100, "Red", "Blue" )
)

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[Epoloni12]

Hi @v-yilong-msft ,

Thank you for the detailed information. 

I have a calendar table and one target file per year (total of 3 excel files) following this standard : 

Period	Cost	Complaints	Productivity	Inspection
Jan/202X	XXX	YYYY	UUU%	ZZZ%

Then i tried to apply the first formula with the values of my targets, but didn't work, do you know what could be the problem, please?

Thank you.

[v-yilong-msft]

Hi @Epoloni12 ,

First of all, there should be a space after the VAR, _A indicates a variable, you can try correcting it this way and see if you can correct the error.

If that still doesn't work, can you provide me with more information or a .pbix file so I can do more in-depth research?

How to Get Your Question Answered Quickly 

Best Regards

Yilong Zhou

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

[Epoloni12]

Hi @v-yilong-msft 

I added the space, but still not working. 

I added here the files which are the basis for my calculation. Further info is ommited and i highlighted in yellow the reffered columns:

https://drive.google.com/drive/folders/1ia5YC9ht0aG_6NluwGEtJfFafAURVH3X

Thank you very much.

[v-yilong-msft]

Hi @Epoloni12 ,

I can not open your .pbix file, can you change a way to upload your .pbix file? If you upload the .pbix file, I will do a deeper research.

Best Regards

Yilong Zhou

[Epoloni12 · Accepted solution]

Hi @v-yilong-msft, 

I found a solution, posting here what worked out:

Thank you for your support. 

[_AAndrade]

Hi,

Can you provide a picture of the output what of you're looking for?