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

[ThisIsHalloween]

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 amount for that week with shades in between to show how they did against each other. So far, I got the location and $0 down but when I have all the weeks visible in the matrix, it ranks the dollars based on every week that is showing. 

Example:

To show you what I want each week to look like, if you use the slicer on the bottom of the page and select one week only, that is how the gradient should look even when all weeks are selected.

Example:

Here is the DAX that is being used for the conditional formatting:

ConFormWC = 

VAR SumTotal = 
SUM('Data by Week'[Total])

VAR Locations = 
IF(SELECTEDVALUE('Data by Week'[Total Rank]) = "No",0,"Welcome Center")

VAR Rk = 
    RANK(
        DENSE,
        ALLEXCEPT('Data by Week','Data by Week'[User ],'Data by Week'[Sorting Location],'Data by Week'[Period Title]),
       ORDERBY('Data by Week'[Sort Loc Num], ASC,'Data by Week'[Total], ASC BLANKS LAST)
    )
    RETURN
    IF(
        OR(
            SumTotal = 0,
            Locations = 0),
            BLANK(),Rk)

What do I need to change to make the ranking look at that week's data only?

I can't upload the pbix file to the forum (I guess I am not a high enough user yet). So here is a link to OneDrive: Anonymous.pbix 

Edit: it seems that the file may not be opening. It worked for me when I tested it in cognito. So, here is a google drive link instead and see if that works. If it doesn't, let me know: https://drive.google.com/file/d/1OIUk8ygcjB306IFblEtlCock4ZKx6qfw/view?usp=sharing 

If that one gives you any issues, here is a Box link: https://app.box.com/s/fv9d7kxdezlt5xkj52al069c9j30in2b  I am trying to maximize my chance of getting this figured out. Thanks!

[lbendlin]

link requires login - please check.

[ThisIsHalloween]

If the google drive gives you issues, I uploaded to Box too: https://app.box.com/s/fv9d7kxdezlt5xkj52al069c9j30in2b 

[lbendlin · Accepted solution]

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])))

[ThisIsHalloween]

That worked! Thank you so much!!!!  I really appreciate it! Now, to sit down and analyze your formula so I know how to do it for next time. 

[lbendlin]

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]

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

[ThisIsHalloween]

That's so weird since I tested in In Cognito and it works on my end. Let's try google drive: https://drive.google.com/file/d/1OIUk8ygcjB306IFblEtlCock4ZKx6qfw/view?usp=sharing . Let me know if that works.