Forum Discussion

kattlees's avatar
kattlees
Post Patron
8 years ago

Cumulative sum with VALUES filter issue?

I have a deduction table.... I'm trying to get a projected total of the deduction value for the year up to 5000. Once it hits 5000 it should stop.

 

So I have the following:

A column:

DedValue=if(table[code]="125CLD",table[deductionamount],0)   gets me the deduction value for each pay period so far

A measure

DedValueTotal=sum(table[DedValue]) - gets me the deduction value so far

To get projected value = I have 

ProjValueActual = ([DedValueTotal]/[PayDatesDone])*[TotalPayDates]

 

Now i want to do a projected for the year with a maximum of 5000.00

I was thinking of a column of

Proj Ded value = if([ProjValueActual > 5000,5000,[ProjValueActual]) but this is not limiting at 5000. It just shows the ProjValueActual

 

Example:

Employee      Code                Deductionamount         Date

Jim                  125CLD              500                             1/15/17

Sue                  125CLD                 50                            1/15/17

Jim                   125CLD              500                             1/30/17

Sue                  125CLD                 50                            1/30/17

Jim                   125CLD              500                            2/15/17

Sue                   125CLD                50                            2/15/17

 

 

Results should be

Employee                 DeductionAmount           ProjValueActual            Proj Ded Value

Jim                           1500                                        12000                         5000    (need to stop at max value)

Sue                           150                                           1200                         1200

 

4 Replies

    • kattlees's avatar
      kattlees
      Post Patron

      In Pay Dates Table

       

      PayDatesDone=sum(PayDates[CountPayDates])

      CountPayDates=if(PayDates[PayDate]<today(),1,0)

       

      PayDatesLeft is a value we enter manually

       

      TotalPayDates = PayDatesDone+PayDatesLeft

  • MarkS's avatar
    MarkS
    Resolver IV

    Hi kattlees,

     

    Would this measure work

    ProjDedValue = MIN(5000,[ProjValueActual])