PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Take the following table

PERIOD AMOUNT PERCENT
1 500 95%
2 500 96%
3 600 90%


Is there a calculated column formula that could be added to do the following? For PERIOD 1, the result is AMOUNT x PERCENT, or 475. For PERIOD 2, the result is (AMOUNT X PERCENT) + (PERIOD 1 CALCULATED AMOUNT X PERCENT), OR (480) + (456). And for PERIOD 3, the "running total" continues and would be (AMOUNT X PERCENT) + (PERIOD 2 CALCULATED AMOUNT x PERCENT), OR (540) + (936 * 90%). The resulting table would look like:

PERIOD AMOUNT PERCENT CALCULATED COLUMN
1 500 95% 475
2 500 96% 936
3 600 90% 1382.4

I've racked my brain but can't figure out how to make this work. Is it possible?

Thanks for any help.

Maybe a simpler question is this - is there any way to create a calculated column that performs a "running product" instead of a running "total"? Say I want to multiply row 1 value by row 2 value by row 3 value - I'm having difficulty finding a way to do that. But I feel like I'm missing something easy - any suggestions?

Hi,

I did not understand how you are getting these numbers, but I'll try to explain with my own numbers, maybe this will help you?

Lets say I have table as you described. I can add to this table 2 calculated columns (for simplicity, you do not really need subAmount):

I can define SubAmount with following DAX formula:
=Table1[Amount] * Table1[Percent]

I can define my CalculatedColumn1 with following DAX formula:
=SuMX(Filter(Table1, Table1[Period] <= EARLIER(Table1[Period])), Table1[SubAmount])

In this formula I am summing all subamounts that have Period less or equal to my current Period value.
Result would be:

Period Amount Percent SubAmount CalculatedColumn1
1 500 0.95 475 475
2 500 0.96 480 955
3 600 0.9 540 1495

Or image:

I also tried FinalAmount DAX formula:

=Table1[SubAmount] + SuMX(Filter(Table1, Table1[Period] < EARLIER(Table1[Period])), Table1[SubAmount]*Earlier(Table1[Percent]))

But results are not as you expected:

1 500 0.95 475 475
2 500 0.96 480 936
3 600 0.9 540 1399.5

So, I cannot figure out how to do that .