Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help on cumulative calculation

My data set is  datset Month value ACT Feb 2 LMN feb 3 UFG Jan 2 ACT Jan 3 ACT Mar 0 LMN Mar 1 ACT April 0   I need column chart with  X axis Month Y a...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can nest an IF function outside the original formula to meet your requirement.

     

    Measure = 
IF (
    MAX ( 'Table'[value] ) = 0,
    0,
    VAR x =
        MIN ( 'Table'[month number] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[month number] <= x )
        )
)

     

    The result like this,

     

     

    For the sample, we add a new row, what is your expected result for May, 6 or 1?

     

     

     

    BTW, pbix as attached.

     

    Best regards,


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

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi Anonymous ,

 

Sorry for that, here is another link, you can try again.

 

Best regards,


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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

hi.. i saw the pbix. My requiremnt is 

 

If I select all the months( ACT dataset),  the value in the bar should come 0 for Mar and April. Though the value for Jan and Feb is correct ( as per cummulative)  .

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

    Hi Anonymous ,

     

    We can nest an IF function outside the original formula to meet your requirement.

     

    Measure = 
IF (
    MAX ( 'Table'[value] ) = 0,
    0,
    VAR x =
        MIN ( 'Table'[month number] )
    RETURN
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[month number] <= x )
        )
)

     

    The result like this,

     

     

    For the sample, we add a new row, what is your expected result for May, 6 or 1?

     

     

     

    BTW, pbix as attached.

     

    Best regards,


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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      perfect