running total based on a customer # colum

[BORUCH]

hi

i have a power piviot table
customer #, invoice # invoice, amount, etc...

i would like a Dax formula that would give me a running total based on my customer # field

At every change in customer # it recalculates FOR EX.

CUSTOMER #	INVOICE #	AMOUNT	RUNNING TOTAL
A001	1	200	200
A001	2	200	400
B001	3	150	150
B001	4	200	350

<tbody>
</tbody>

Any help is greatly appreciated

 

[BORUCH]

any updates i have not gotten any response

 

[Matt Allington]

Read my article here
DAX Time Intelligence Explained - Excelerator BI
i cover a year to date pattern in this article and you can use this for your needs

but it depends how you want to visualise this. You should first work out the visual you want and then write a formula to work.

 

[BORUCH]

i'm not using a date column that is the biggest problem its by customer #

 

[BORUCH]

i'm not using a date column that is the biggest problem its by customer #

any updates to my question?

 

[Matt Allington]

How do you want to visualise this? Your question shows a table, but you don't need the running total materialised in a table. So it depends how you want to visualise this as to what the measure needs to be. If you want to place customer on rows In a pivot and see the running total, you could use something like this

=calculate(sum(table[amount]),filter(allexcept(table,table[customer]),table[invoice num] <= max(table[invoice num]))

 

[BORUCH]

How do you want to visualise this? Your question shows a table, but you don't need the running total materialised in a table. So it depends how you want to visualise this as to what the measure needs to be. If you want to place customer on rows In a pivot and see the running total, you could use something like this

=calculate(sum(table[amount]),filter(allexcept(table,table[customer]),table[invoice num] <= max(table[invoice num]))

it doesn't seem to work I would like it as a calculated column

 

[Matt Allington]

I'm happy to help debug why it doesn't work if you post a sample work book. I can't bring myself to help you with a calculated column as to me this is like asking a steak chef for tomato sauce/ketchup

 

[akice]

While i totally agree with Matt Allington that you should create a measure for this and not a calculated column, i'm just a corner hotdog stand chef so while i have not tested it, this calculated column should work:

=calculate(sum(Table[AMOUNT]),filter(allexcept(table,Table[CUSTOMER #]),Table[INVOICE #] <= EARLIER(Table[INVOICE #])))

But again you should use a measure...