Color Table on quarter value and records value

[Anonymous]

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

[Icey]

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

[Icey]

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.

[amitchandak]

@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