Forum Discussion
Cumulative Sum by 2 columns
Hi Folks and Fowmy Jihwan_Kim
I have a requirement where i have to get cumulative sum by 2 columns. The below is the sample data
The thing is Final Amount is coming by Customer Number and Year
| Year | Customer Number | Price Amt | Diff | Balance Pay | Final Amount |
| 2015 | 123 | $1000 | -50 | $500 | $450 |
| 2016 | 123 | $1000 | 100 | $600 | -$50 |
| 2017 | 123 | $1000 | 200 | $200 | -$50 |
| 2015 | 789 | $2000 | 800 | $100 | $2700 |
| 2016 | 789 | $2000 | 700 | $300 | $3100 |
| 2017 | 789 | $2000 | 600 | $400 | $3700 |
I want to get Final Amount calculation as below:
1sr row calculation = $1000 +(-50) - $500 = $450
2nd row calculation = $1000 + ((-50)+100) - ($500+$600) = -50
3rd row calculation = $1000 + (-50+100+200) - ($500+$600+$200) = -50
4th row calculation = $2000+800 - $100 = $2700
5th row calculation = $2000+(800+700) - ($100+$300) = $3100
6th row calculation = $2000 + (800+700+600) - $400 = $3700
Hi kbabu57
Ashish_Mathur Good share!
For your question, here is the method I provided:
Here's some dummy data
"Datas"
You can create a measure. Group by customer number, and add up "Diff" and "Balance Pay".
Final Amount = CALCULATE ( SUM ('Datas'[Price Amt]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] = MAX ('Datas'[Year]) ) ) + CALCULATE ( SUM ('Datas'[Diff]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] <= MAX ('Datas'[Year]) ) ) - CALCULATE ( SUM ('Datas'[Balance Pay]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] <= MAX ('Datas'[Year]) ) )Here is the result
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Friends, Could you please help how to write the DAX measure for the above requirement
- Ashish_Mathur
Super User
Hi,
PBI file attached.
Hope this helps.
Hi Ashish Ashish_Mathur
1st of all , Thank you so much for replying my thread. Sorry for delayed reply as i am trying out with any options & combinations with the above pbi file which you attached. Seems like, you created a extra table called "Date" and developed the logics to achieve the requirement. Is there any way without creating extra "date" and build the logic with the existing data ?
I will take a step back. I have only customer numbers as below and want to achieve the requirement.Please provide your thoughts how can we get with the below dataCustomer
Number
Price Amt Diff Balance Pay Final Amount abc123 $1000 -50 $500 $450 abc123 $1000 100 $600 -$50 abc123 $1000 200 $200 -$50 def567 $2000 800 $100 $2700 def567 $2000 700 $300 $3100 def567 $2000 600 $400 $3700
Thanks in advance- Ashish_Mathur
You are welcome. You must create a Calendar Table.
Hi Ashish Ashish_Mathur
Thank you. Much appreciated for your help. However, i just tried without creating extra Calendar table, with the existing data that has date column, got the required output as well 🙂
Hi kbabu57
Ashish_Mathur Good share!
For your question, here is the method I provided:
Here's some dummy data
"Datas"
You can create a measure. Group by customer number, and add up "Diff" and "Balance Pay".
Final Amount = CALCULATE ( SUM ('Datas'[Price Amt]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] = MAX ('Datas'[Year]) ) ) + CALCULATE ( SUM ('Datas'[Diff]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] <= MAX ('Datas'[Year]) ) ) - CALCULATE ( SUM ('Datas'[Balance Pay]), FILTER ( ALLEXCEPT ('Datas', 'Datas'[Customer Number]), 'Datas'[Year] <= MAX ('Datas'[Year]) ) )Here is the result
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow.. This is even better. Thank you Nono Chen
