Set up conditional colour formatting for each individual row in the table?

[shrutiiyer]

Hello BI Experts! I'm working on a table/,matrix visualization in PowerBI to display website traffic growth by month with rows set to be individual landing pages. There are quite a few pages and we'd like it to visualize the change over time with condition colour formatting of the cells. However, there is a lot of disparity between groups of pages as some are in the 10000+ range with others less than 100. But it's important to visualize the change over time for the lower traffic pages as well as the traffic is significant less in volume but much higher in conversion potential. Does anyone know how to set up conditional colour formatting for each individual row in the table? Or at least point me to a helpful resource /template/example?

[Icey]

Hi @shrutiiyer ,

Try to create a measure like so:

Color = 
VAR MaxVal =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Keyword] )
    )
VAR MinVal =
    CALCULATE (
        MIN ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Keyword] )
    )
RETURN
    DIVIDE ( MAX ( 'Table'[Value] ) - MinVal, MaxVal - MinVal )

BTW, .pbix file attached.

Best Regards,

Icey

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

[shrutiiyer]

Hey @Icey! Thanks so much for sharing the .pbix... So I tried to replicate the same measure and for some reason the Min value is not working. 

We are pulling in data from a database (Azure Web Services) and doesnt work then. Later what I did was -  export a few rows of the table to a csv and then created a new .PBIX and it worked. 

Any idea why the measure is not working when I get data from Azure Web Services? Thanks so much for taking time out and sharing the .pbix! 

[Icey]

Hi @shrutiiyer ,

Is your "Month Year" column from another table?

If it is, please try this:

Color = 
VAR MaxVal =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Keyword] ),
        ALL ('Another Table'[Month Year])
    )
VAR MinVal =
    CALCULATE (
        MIN ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Keyword] ),
        ALL ('Another Table'[Month Year])
    )
RETURN
    DIVIDE ( MAX ( 'Table'[Value] ) - MinVal, MaxVal - MinVal )

Best Regards,

Icey

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

[shrutiiyer]

Thanks for getting back to me @Icey  The "Month Year" column is from the same table. I am choosing Google Analytics to 'Get Data'. Maybe that is why it is not working. I tried many times. 

When I import data from Google Analytics and save locally to my PC then it works. 

Unfortunately cannot share the PBIX as the data is coming from Google Analytics. Although, here are a few screenshots. If you look at the rows blog - it does not calculate 6 as high, for the second last and last row also something is going wrongthe colour measurethe colour measure in the conditional formatting pane

[Icey]

Hi @shrutiiyer ,

I find that you drill down by the "Date" column. So, try this:

Color = 
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "Sum_Y",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        RETURN
            SUMX ( FILTER ( 'Table', [Keyword] = key_ && [Date].[Year] = y_ ), [Value] ),
        "Sum_Q",
        VAR key_ = [Keyword]
        VAR q_ = [Date].[Quarter]
        RETURN
            SUMX ( FILTER ( 'Table', [Keyword] = key_ && [Date].[Quarter] = q_ ), [Value] ),
        "Sum_M",
        VAR key_ = [Keyword]
        VAR m_ = [Date].[Month]
        RETURN
            SUMX ( FILTER ( 'Table', [Keyword] = key_ && [Date].[Month] = m_ ), [Value] )
    )
VAR t2 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Sum_Y",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        RETURN
            SUMX ( FILTER ( ALL ( 'Table' ), [Keyword] = key_ && [Date].[Year] = y_ ), [Value] ),
        "Sum_Q",
        VAR key_ = [Keyword]
        VAR q_ = [Date].[Quarter]
        RETURN
            SUMX ( FILTER ( ALL ( 'Table' ), [Keyword] = key_ && [Date].[Quarter] = q_ ), [Value] ),
        "Sum_M",
        VAR key_ = [Keyword]
        VAR m_ = [Date].[Month]
        RETURN
            SUMX ( FILTER ( ALL ( 'Table' ), [Keyword] = key_ && [Date].[Month] = m_ ), [Value] )
    )
VAR t3 =
    ADDCOLUMNS (
        t1,
        "Sum_Y_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Y] ),
        "Sum_Q_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Q] ),
        "Sum_M_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_M] ),
        "Sum_Y_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Y] ),
        "Sum_Q_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Q] ),
        "Sum_M_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_M] )
    )
VAR t4 =
    ADDCOLUMNS (
        t3,
        "D_Y", DIVIDE ( [Sum_Y] - [Sum_Y_Min], [Sum_Y_Max] - [Sum_Y_Min] ),
        "D_Q", DIVIDE ( [Sum_Q] - [Sum_Q_Min], [Sum_Q_Max] - [Sum_Q_Min] ),
        "D_M", DIVIDE ( [Sum_M] - [Sum_M_Min], [Sum_M_Max] - [Sum_M_Min] )
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[Date].[Year] ), MAXX ( t4, [D_Y] ),
        ISFILTERED ( 'Table'[Date].[Quarter] ), MAXX ( t4, [D_Q] ),
        ISFILTERED ( 'Table'[Date].[Month] ), MAXX ( t4, [D_M] )
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[shrutiiyer]

@Icey Thank you so much for sharig the .pbix file! We are trying to drill to 'expand all down one level hierarchy'. The end user wants to see all months of 2018,2019 etc. I've shared a screenshot of the error. 

The measure is working fine when I select ''go to next level hierarchy''' but does not work when i select ''expand all one level down''

[Icey · Accepted solution]

Hi @shrutiiyer ,

If so, it is needed to change the measure like so:

Color =
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "Sum_Y",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        RETURN
            SUMX ( FILTER ( 'Table', [Keyword] = key_ && [Date].[Year] = y_ ), [Value] ),
        "Sum_Q",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        VAR q_ = [Date].[Quarter]
        RETURN
            SUMX (
                FILTER (
                    'Table',
                    [Keyword] = key_
                        && [Date].[Year] = y_
                        && [Date].[Quarter] = q_
                ),
                [Value]
            ),
        "Sum_M",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        VAR q_ = [Date].[Quarter]
        VAR m_ = [Date].[Month]
        RETURN
            SUMX (
                FILTER (
                    'Table',
                    [Keyword] = key_
                        && [Date].[Year] = y_
                        && [Date].[Quarter] = q_
                        && [Date].[Month] = m_
                ),
                [Value]
            )
    )
VAR t2 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Sum_Y",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        RETURN
            SUMX (
                FILTER ( ALL ( 'Table' ), [Keyword] = key_ && [Date].[Year] = y_ ),
                [Value]
            ),
        "Sum_Q",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        VAR q_ = [Date].[Quarter]
        RETURN
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    [Keyword] = key_
                        && [Date].[Year] = y_
                        && [Date].[Quarter] = q_
                ),
                [Value]
            ),
        "Sum_M",
        VAR key_ = [Keyword]
        VAR y_ = [Date].[Year]
        VAR q_ = [Date].[Quarter]
        VAR m_ = [Date].[Month]
        RETURN
            SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    [Keyword] = key_
                        && [Date].[Year] = y_
                        && [Date].[Quarter] = q_
                        && [Date].[Month] = m_
                ),
                [Value]
            )
    )
VAR t3 =
    ADDCOLUMNS (
        t1,
        "Sum_Y_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Y] ),
        "Sum_Q_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Q] ),
        "Sum_M_Max", VAR key_ = [Keyword] RETURN MAXX ( FILTER ( t2, [Keyword] = key_ ), [Sum_M] ),
        "Sum_Y_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Y] ),
        "Sum_Q_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_Q] ),
        "Sum_M_Min", VAR key_ = [Keyword] RETURN MINX ( FILTER ( t2, [Keyword] = key_ ), [Sum_M] )
    )
VAR t4 =
    ADDCOLUMNS (
        t3,
        "D_Y", DIVIDE ( [Sum_Y] - [Sum_Y_Min], [Sum_Y_Max] - [Sum_Y_Min] ),
        "D_Q", DIVIDE ( [Sum_Q] - [Sum_Q_Min], [Sum_Q_Max] - [Sum_Q_Min] ),
        "D_M", DIVIDE ( [Sum_M] - [Sum_M_Min], [Sum_M_Max] - [Sum_M_Min] )
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[Date].[Month] ), MAXX ( t4, [D_M] ),
        ISFILTERED ( 'Table'[Date].[Quarter] ), MAXX ( t4, [D_Q] ),
        ISFILTERED ( 'Table'[Date].[Year] ), MAXX ( t4, [D_Y] )
    )

Best Regards,

Icey

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

[shrutiiyer]

Thank you so much @Icey ! It is working 😁. I am having a tough time understanding the measure. So looking at a few DAX videos to understand the steps you've taken. We have been trying to figure out for months! And we finally have it. Thank you again from our entire team!!

[Icey]

You are welcome.😉

[amitchandak]

@shrutiiyer , Create a color measure and use in conditional formatting "Field"

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

Refer:

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[shrutiiyer]

@amitchandak - Sorry! I'm new to Power BI and did not quite understand the solution you shared. Any chance you can share a pbix file with me? I visited the links you shared and it seems like those work for columns and not for rows?