Forum Discussion

haymanezzeldin's avatar
haymanezzeldin
Helper I
9 years ago
Solved

Cumulative column after name filter

Hello,

I would like to create a column that calculates cumulative values after filtering the ProjectName column.

 

 

 

So for example, if I filter the ProjectName column and choose PRO_01_OnTraget, then the result should be like that:

 

 

 

I would appreciate your assistance so much.

 

Best regards,

Hayman

Need Help
  • haymanezzeldin's avatar
    haymanezzeldin
    9 years ago

    I found how it should be:

    Since the filter is based on a Text value (not date nor numerical), then filter using the "Values" function

    So it should be:

     

    =CALCULATE(SUM('Table1'[NonCumulativeValues] ), FILTER ( ALL ( 'Table1' ), [StartPeriod] <= MAX ( [StartPeriod] )), VALUES ( 'Table1'[ProjectName] ) ) ) 

     

    Best regards,

    Hayman

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    What determines the order?  Do you have date column that can be used?

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Give this measure a crack.  I think it might be close

         

        Measure =
CALCULATE (
    SUM ( 'Table1'[NonCumulativeValues] ),
    FILTER (
        ALL ( 'Table1' ),
        [StartPeriod] <= MAX ( [StartPeriod] )
            && Table1[ProjectName] = MAX ( 'Table1'[ProjectName] )
    )
)