Forum Discussion

ThisIsHalloween's avatar
ThisIsHalloween
Icon for Helper I rankHelper I
2 years ago
Solved

Ranking Formula by Week Help Needed (pbix file link attached)

I am trying to have a conditional formatting to rank users by week in the "Welcome Center" location only and to ignore $0 or blanks. Red is the lowest amount of the week and green is the highest amou...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Conform = 
var a = CALCULATETABLE(VALUES(Employees[User ]),REMOVEFILTERS(Employees[User ]))
var b = ADDCOLUMNS(a,"t",CALCULATE(sum('Data by Week'[Total])))
return if(max('Sorting Location'[Sorting Location])="Welcome Center",divide(sum('Data by Week'[Total])-minx(b,[t]),maxx(b,[t])-minx(b,[t])))
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

you can use DAXFormatter to make it look nicer.

Conform =
VAR a =
    CALCULATETABLE (
        VALUES ( Employees[User ] ),
        REMOVEFILTERS ( Employees[User ] )
    )
VAR b =
    ADDCOLUMNS ( a, "t", CALCULATE ( SUM ( 'Data by Week'[Total] ) ) )
RETURN
    IF (
        MAX ( 'Sorting Location'[Sorting Location] ) = "Welcome Center",
        DIVIDE (
            SUM ( 'Data by Week'[Total] ) - MINX ( b, [t] ),
            MAXX ( b, [t] ) - MINX ( b, [t] )
        )
    )

VAR a - collects all users for the current week by removing the filter on User but keeping all other filters

VAR b - calculates the total value of each of these users

RETURN statement:  Instead of a ranking you do a sort of percentile-ing, from 0 to 1.  You do that by finding the minimum and maximum values for that week, and then scale them so the minimum is 0 and the maximum is 1.  The actual value will be in between, and can now serve as the basis for the color formatting in a consistent manner , regardless of the actual data ranges per week (and conveniently ignoring the blanks too).

ThisIsHalloween's avatar
ThisIsHalloween
Icon for Helper I rankHelper I
2 years ago

Thank you for breaking it down! That is such a huge help!