Forum Discussion

Epoloni12's avatar
Epoloni12
Frequent Visitor
2 years ago
Solved

CONDITIONAL FORMATTING FOR YEARS WITH DIFFERENT TARGETS

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.

Need Help
  • Epoloni12's avatar
    Epoloni12
    2 years ago

    Hi Anonymous, 

     

    I found a solution, posting here what worked out:

     

     

    Thank you for your support. 

7 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Epoloni12
      Frequent Visitor
      2 years ago

      Hi Anonymous ,

       

      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 : 

      PeriodCostComplaintsProductivityInspection
      Jan/202XXXXYYYY 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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.