topic Re: Comparison with previous value in matrix and show up down icon in DAX Commands and Tips

Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 06:50:14 GMT

Hi,

I have created a matrix and in that matrix am trying to compare the value in W2 column with the value in W1 column (means current column with the previous column) and based on that am trying to show up, down arrow icon. I am able to create the matrix but not able to do the comparison part and icon showing part. Can anyone please help me. I have attached sample pbix file and screenshot

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 06:57:33 GMT

HI Anonymous ,

See if this helps

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

https://www.youtube.com/watch?v=BbuikATa3ow

https://www.youtube.com/watch?v=MAISRa88bl0

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 07:28:00 GMT

I know how to apply conditional formatting but the thing is here I want to compare the values in a column based on other column

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 07:42:48 GMT

Hi Anonymous Can you share the pbix file again I can't see that.

Thanks

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 07:56:53 GMT

You can download the file from below link:

http://dl.dropboxusercontent.com/s/btmcxqq9bzg5kr4/Test.pbix?dl=0

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 09:30:44 GMT

Hi Anonymous

Try this measure.

Compare W2 With W1 = VAR WAVE = SELECTEDVALUE ( Sheet1[Wave] ) VAR Country = SELECTEDVALUE ( Sheet1[Country] ) VAR s = CALCULATE ( SUM ( Sheet1[Sales] ) ) VAR FiltereTable1 = FILTER ( ALL ( Sheet1 ), Sheet1[Wave] = "W1" && Sheet1[Country] = Country ) VAR W1 = SUMX ( FiltereTable1, Sheet1[Sales] ) VAR RESULT = SWITCH ( TRUE () , WAVE = "W2" && s > W1, 1 , WAVE = "W2" && s <= W1, 2 , 0 ) RETURN RESULT

Apply background color conditional formatting as below to get the idea how it works.

This works in case you just want to Compare W2 with W1. However there are other scenarios like, if you want to compare (W3 with W1) or (W4 with W1) and so on; then you will have to add more conditions in SWITCH.

If that's the case you should also consider that how this can be tackled when your Waves Column will have more values like(W5, W6 ... Wn).

Thanks

Re: Comparison with previous value in matrix and show up down icon

Anonymous — Thu, 30 Jul 2020 10:05:25 GMT

Thanks for the reply but this can be applied when you know the number of waves you have. Right now we have 8 waves and in future there will be many more then how we will tackle this? And the solution you have provided is am already using as an alternative.