Cumulative Total by Person

[Anonymous]

Hi, Is there an easy way to make a cumulative total by sales person? So in my pivot table, I have a list of multiple sales people. I'd like to know what each individual's YTD bookings number is. This is what I've tried so far, but it only works for the first name in the pivot and after that just does the cumulative overall total and doesn't follow the sales person name at all.

=CALCULATE(sum(BookingsTable[Product Bookings]),filter(all(BookingsTable),BookingsTable[PO Date]<=max(BookingsTable[PO Date])&&BookingsTable[Name]<=max(BookingsTable[Name])))

I'd like it to look something like the following:

Name	PO Date	Product Bookings	YTD Booking Amount
Joe	1/1/2020	50	50
Joe	2/1/2020	100	150
Joe Total		150
Mary	1/1/2020	50	50
Mary	2/1/2020	150	200
Mary	3/1/2020	200	400
Mary Total		400

I also want to know if there is a way to cap the cumulative total when it hits the quota. Is there an easy way to add that in as well?

Thanks for the help!

[jnixon]

Hi Joy,

I'd suggest you use the built-in time intelligence function TOTALYTD:

YTD_bookings =
TOTALYTD (
   SUM ( BookingsTable[Product Bookings]) ,
   BookingsTable[PO Date]
)

But it's always best to build a Date table and join it to your fact tables (like bookings).  Then you can use all the time intelligence functions without fail: https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

Also, you might consider a measure [Total Bookings] that you can use as a parameter in the above measure.

If you like the solution, please let me know (and mark it) so that I can become a Silver Contributor.

Kind regards,

Jeff

[Anonymous]

Thank you for the response, however, this does not address the requirement to have the YTD amount calculate for each individual name in the table. Do you know how to add a filter to do that? I also want it to show order number in the table as well, but no filtering required for that.

[jnixon]

Hi @Anonymous,

If you  create the measure exactly as I posted, then you can use it in a table or matrix or any other visual and it will give you the result exactly as you are looking for according to your original post.  The visual will provide the filter context for the individual name.

[Anonymous]

Thank you - I see that it does work if I only have the PO date in the matrix, however, when I add in order ID, it messes everything up. Do you know how to do it while showing the Order ID in the matrix as well? I'd like to not have to show PO date in the matrix too if I don't have to. I appreciate your help!

[jnixon]

Hi @Anonymous ,

I think I figured it out!  It is returning the table you want with PO only, no date.

Here is the DAX:

Accumulated bookings =

VAR

YTDBookings =

TOTALYTD (

SUM ( BookingsTable[Product Bookings]) ,

BookingsTable[PO Date]

)

VAR

myLastDate =

MAX(BookingsTable[PO Date])

RETURN

CALCULATE(

sumx(

BookingsTable,

BookingsTable[Product Bookings]

),

FILTER(

ALLEXCEPT(BookingsTable,BookingsTable[Name]),

BookingsTable[PO Date] <= myLastDate

)

)

[jnixon]

That's because you are aggregating over dates.  If you remove the date and add the PO nr, DAX will aggregate YTD bookings for that PO nr, which is just the amount for the PO itself.  I'm not sure how to make something accumulate over a name/date based on PO nr.  The calculation would definitely be more complex.  Sorry for my inexpertise...

[camargos88]

Hi @Anonymous ,

Try creating this measure:

_RT = CALCULATE(SUM('Table'[Product Bookings]); FILTER(ALLEXCEPT('Table';'Table'[Name]); 'Table'[PO Date] <= MAX('Table'[PO Date])))

Ricardo

[Anonymous]

Thank you for the revised formula, however, it still isn't working. Could it be because I want the table to show all of the names, so there isn't a filter on there in the first place anyways? I also forgot to show earlier, but I do want order number in the table too - could that be messing it up?

Here's the measure I created: I think it's the same as yours.

YTDProductBookings = CALCULATE(sum(BookingsTable[Product Bookings]),filter(ALLEXCEPT(BookingsTable,BookingsTable[Name]),BookingsTable[PO Date]<=max(BookingsTable[PO Date])))