Comparison with previous value in matrix and show up down icon

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

Thanks

[Anonymous]

You can download the file from below link:

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

[harshnathani]

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)

[Anonymous]

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