Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Color Table on quarter value and records value

Hi All

 

I would to color my table on the basis of current quarter value.

Let's say I have a table that contains records on the basis of year-quarter segregation and I would like to color 2020 Q2 with yellow color coding.

 

Please refer the above table and I would like to color full column cell of Year 2020 Quarter 2 with Yellow color entirely even where there is no value. The result table should look like this:-

 

Is there any way we could go column based cell coloring in PowerBI?

 

Apart from this I would also like to color cells on the basis of records values as well i.e., NUM > 20 and NUM < 30 and YEAR = 2020 then I want that records value(by refering above table, 2.1 and 1.7 ) to be in purple color and other conditions for other colors?

The table shouls look like this:-

 

 

Any help would be appreciated.

 

 

Thank You

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    I have two different examples. You have to create one out of it. On Qtr name use firstnonblank and use a measure and create a color measure.

    Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
    Color Year = if(FIRSTNONBLANK('Date'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Date'[Year],2014)>2018,"red","yellow"))

    In conditional formatting of the matrix under advance control, choose the field and choose this measure for each column

     

    Refer : https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

     

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Based on my test, your requirements cannot be fully met. Please check:

     

    - For blank values,

    1. They won't be set conditional format unless you choose "As zero" for "Default formatting". But it will be applied on all blank values.

     

    2. Or, you can put the conditional measure in the matrix visual. Be careful of blank values.

    Measure 1 = 
IF (
    MAX ( 'Table'[Years] ) = 2020
        && MAX ( 'Table'[Quarters] ) = "Q2",
    1,
    IF (
        MAX ( 'Table'[NUM] ) > 20
            && MAX ( 'Table'[NUM] ) < 30
            && MAX ( 'Table'[Years] ) = 2020
            && MAX ( 'Table'[Value] ) <> BLANK(),
        2,
        IF (
            MAX ( 'Table'[NUM] ) > 30
                && MAX ( 'Table'[Years] ) = 2021
                && MAX ( 'Table'[Quarters] ) = "Q2"
                && MAX ( 'Table'[Value] ) <> BLANK(),
            3,
            IF (
                MAX ( 'Table'[NUM] ) > 20
                    && MAX ( 'Table'[NUM] ) < 30
                    && MAX ( 'Table'[Years] ) = 2021
                    && MAX ( 'Table'[Quarters] ) = "Q4"
                    && MAX ( 'Table'[Value] ) <> BLANK(),
                4
            )
        )
    )
)

    Measure 2 = 
IF (
    MAX ( 'Table'[Years] ) = 2020
        && MAX ( 'Table'[Quarters] ) = "Q2",
    1,
    IF (
        MAX ( 'Table'[NUM] ) > 20
            && MAX ( 'Table'[NUM] ) < 30
            && MAX ( 'Table'[Years] ) = 2020,
        2,
        IF (
            MAX ( 'Table'[NUM] ) > 30
                && MAX ( 'Table'[Years] ) = 2021
                && MAX ( 'Table'[Quarters] ) = "Q2",
            3,
            IF (
                MAX ( 'Table'[NUM] ) > 20
                    && MAX ( 'Table'[NUM] ) < 30
                    && MAX ( 'Table'[Years] ) = 2021
                    && MAX ( 'Table'[Quarters] ) = "Q4",
                4
            )
        )
    )
)

    - For overlapping cases,  the rule that appears first shall prevail.

     

     

    For more details, please check the attached PBIX file.

     

     

    Best Regards,

    Icey

     

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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Is this problem solved?


    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


    If not, please let me know.

     


    Best Regards,
    Icey