Dynamic conditional format every 1/3 rows in a table

DAX DAX Calculations
1

Hello,

I’m trying to do a conditional formatting on a table creating a measure that would let me color code every 1/3 rows in a table.

For example,
I have a year column which is being used as a slicer. And I have a table with a list of employees and their data which tends to increase or decrease in rows based on the year selected in the slicer. I’m trying to create a measure that would color code every 1/3 rows of employees.

An example would be for the year 2019, I have 9 rows of employees. And the table shows red for the first 3 employees, green for the next 3 and then yellow for the 3 afterwards. However, when the user selects the year 2020, I have 35 rows of employees. How would I make it dynamic so it automatically colors them?

2

@supergallagher25 ,

Super interesting problem. See how this works for you.

image

Basically what I did was a RANKX on the ALLSELECTED Customer Names, ranked by Total Sales, but with a random component in the cents column as not to affect the overall order, but prevent ties since ties will screw up the division by thirds.

Here are the three primary measures:

The first ranks the customer names by total sales to get the number of rows:

Rank Customers = 
RANKX(
    ALLSELECTED( Customers[Customer Names] ),
    [Total Sales Plus Rand] ,,
    DESC,
    Dense
)

The second dynamically gets the max row number to divide that into the top one third and the second one third:

Max Rank = VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                ALLSELECTED( 'Customers'[Customer Names] ),
                [Total Sales] <> BLANK()
            ),
            Customers[Customer Names]
        ),
        "@TotSales", [Total Sales Plus Rand],
        "@RankCust", [Rank Customers]
    )
VAR Result =
    MAXX( vTable, [@RankCust] )
RETURN
    Result

And the final measure does the conditional formatting by thirds:

CF Thirds = 
IF( HASONEVALUE( Customers[Customer Names] ),
    SWITCH( TRUE(),
        [Rank Customers] <= [Top Third], "Red",
        [Rank Customers] <= [Second Third], "Green",
        "Yellow"
), BLANK()
)

I hope this is helpful. Full solution file attached below.

– Brian
e DNA Forum – Highlight Top Third Rows Solution.pbix (128.9 KB)

3 Likes
3

Thank you, this helped out a lot!

4

@supergallagher25,

Great - glad to hear that! This is a really interesting problem with a lot of different facets - you might see this one as a video in the coming weeks…

  • Brian
1 Like
5

@supergallagher25

As @BrianJ mentioned it’s really interesting. Here is another solution

image
image1253×706 38.3 KB

Highlight Top Third Rows Solution.pbix (127.6 KB)

2 Likes