cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees Member
Member

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 4
Super User
Super User

Re: Cumulative sum with VALUES filter issue?

What are your formulas for PayDatesDone and TotalPayDates?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


kattlees Member
Member

Re: Cumulative sum with VALUES filter issue?

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 Member
Member

Re: Cumulative sum with VALUES filter issue?

Hi @kattlees,

 

Would this measure work

ProjDedValue = MIN(5000,[ProjValueActual])

 

 

kattlees Member
Member

Re: Cumulative sum with VALUES filter issue?

Gives me the same thing.  Smiley Sad