Conditional Formatting Matrix Issue

[ThisIsHalloween]

I need conditional formatting on the below matrix by week number. 

I have set up as gradiant from red to white to green. 

The problem is that it clearly is looking at the sums by location based on all of the data rather than looking at it by week number since there is no green in most weeks.

The week number is located in a date table and the date table is marked as such. I posted what my date table looks like in case it matters.

How would I go about fixing this? I am still very new to PowerBI.

[ThisIsHalloween]

I gave that a try and things improved but I need to also have it ignore the locations that did not collect anything (so all the 0s or blanks). How would I go about that?

[NaveenGandhi]

Hi 

Create a column by concatenating your week and location as below.

Then based of this sales and this column create a measure to rank your weekly sales.

RankWeekSales =

RANKX(

    ALLEXCEPT('Table', 'Table'[Location], 'Table'[Locationweek]),

    CALCULATE(SUM('Table'[Sales])),

    ,

    DESC,

    Skip

)
Conditional format based on the above measure and you get below result.

   Is it what you are looking for, Let me know if you still need some help.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[ThisIsHalloween]

I gave that a try and things improved but I need to also have it ignore the locations that did not collect anything (so all the 0s or blanks). How would I go about that?

[NaveenGandhi · Accepted solution]

Hi

You can use a if or swtich to achieve this, Below measue should work. Notice that i have changed the whole base measure for more efficiency. 

Rankweek =

Var Rk = RANK (

    DENSE,

    ALLEXCEPT ( 'Rank', 'Rank'[Location] ),

    ORDERBY ( [Sales#], DESC BLANKS LAST )

)

return if(or([Sales#]=0,[Sales#]=BLANK()),BLANK(),Rk)

  

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[ThisIsHalloween]

Thank you!!!! I appreciate your help so much!!!