Forum Discussion

rashel2002's avatar
rashel2002
Icon for Helper II rankHelper II
8 years ago
Solved

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

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!

Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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