Forum Discussion

Johonnatan's avatar
Johonnatan
Icon for Helper I rankHelper I
3 years ago
Solved

Matrix with previous calculation

Hi there

 

I am currently working on a solution that needs to sum activities hours per user and makes a calculation based on static expected hour. Basically, a user can work in different activities and will be expected see a subtotal subtracting from expected hour on a decreasing scale.

 

In this example, you can find a data sample.

 

For Jsilva, I expected see on total column:

 

#1 line - Hours - Expected = 10 - 1 = 9

#2 line - Hours - Expected = 9 (previous calculation) - 3 = 6

#3 line - Hours - Expected = 6 (previous calculation) - 4 = 2

 

Is there a way to do that?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Johonnatan ,

     

    Please try:

    First create an index column:

    Then apply the measure:

    Measure = SELECTEDVALUE('Table'[Expected])-SUMX(FILTER(ALL('Table'),[Index]<=SELECTEDVALUE('Table'[Index])&&[User]=SELECTEDVALUE('Table'[User])),[Hours])

    Final output:

    Best Regards,

    Jianbo Li

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

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Johonnatan ,

     

    Please try:

    First create an index column:

    Then apply the measure:

    Measure = SELECTEDVALUE('Table'[Expected])-SUMX(FILTER(ALL('Table'),[Index]<=SELECTEDVALUE('Table'[Index])&&[User]=SELECTEDVALUE('Table'[User])),[Hours])

    Final output:

    Best Regards,

    Jianbo Li

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