topic Re: Conditional Formatting Matrix Issue in DAX Commands and Tips

Conditional Formatting Matrix Issue

ThisIsHalloween — Mon, 24 Jun 2024 17:32:55 GMT

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.

Re: Conditional Formatting Matrix Issue

NaveenGandhi — Mon, 24 Jun 2024 18:11:31 GMT

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!!

Re: Conditional Formatting Matrix Issue

ThisIsHalloween — Tue, 25 Jun 2024 12:13:59 GMT

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?

Re: Conditional Formatting Matrix Issue

ThisIsHalloween — Tue, 25 Jun 2024 12:37:43 GMT

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?

Re: Conditional Formatting Matrix Issue

NaveenGandhi — Tue, 25 Jun 2024 16:16:52 GMT

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!!

Re: Conditional Formatting Matrix Issue

ThisIsHalloween — Wed, 26 Jun 2024 13:26:26 GMT

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