Help-How to change values to zero using measure on measure

[rashel2002]

Hi everyone!

i'm trying to create new column out of an existing column (cumulative column which was created by measure) - and change all the values that folowing the max value - to zero. here's an example-

Another option that might help is to keep the table as it is (not changing anything), BUT when i'm using the data to create "Line & stacked column chart" - it will look like we're using the right table.

thank you for your help!

[v-yulgu-msft · Accepted solution]

Hi @rashel2002,

Since some rows have the same cumulative value with previous row, I assume the hour number for current WW is 0. So, I created below dummy data.

        

In such a scenario, please create measures:

cumulative No. of hours =
IF (
    SELECTEDVALUE ( 'Hour table'[WW] )
        > CALCULATE (
            LASTNONBLANK ( 'Hour table'[WW], 1 ),
            FILTER ( ALL ( 'Hour table' ), 'Hour table'[hours] <> 0 )
        ),
    0,
    CALCULATE (
        SUM ( 'Hour table'[hours] ),
        FILTER ( ALL ( 'Hour table' ), 'Hour table'[WW] <= MAX ( 'Hour table'[WW] ) )
    )
)

Best regards,

Yuliana Gu

[Stachu]

can you post the original data table and syntax for the cumulative measure? Otherwise it's difficult to answer your question