Forum Discussion

oren's avatar
oren
Helper III
6 years ago
Solved

differences between rows in matrix

Hi   i have the attache table : i what to have another table that will show me the differences between row 1 to 2  and then another row that show the change between 2 to 3 , 3 to 4 , etc.. ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi oren ,

    I created a calculated column to implement it. Please modify the formula based on your data model and have a try.

    difference = 
var a = CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[ID] = EARLIER('Table'[ID])-1))
return
IF(a= BLANK(),BLANK(),'Table'[Sales] - a)

    If the ID column in my sample is not same as yours, you could try to create another column using RANKX to implement.

    RANKX = RANKX(FILTER('Table','Table'[Item] = EARLIER('Table'[Item])),'Table'[Sales],,ASC,Dense)

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

oren's avatar
oren
Helper III
6 years ago

i try this - but it didn't work.

i probably need something also for the columns filter..

 

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi oren ,

    I created a calculated column to implement it. Please modify the formula based on your data model and have a try.

    difference = 
var a = CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[ID] = EARLIER('Table'[ID])-1))
return
IF(a= BLANK(),BLANK(),'Table'[Sales] - a)

    If the ID column in my sample is not same as yours, you could try to create another column using RANKX to implement.

    RANKX = RANKX(FILTER('Table','Table'[Item] = EARLIER('Table'[Item])),'Table'[Sales],,ASC,Dense)

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.