Forum Discussion
SUM PAYMENT
Hi, I have some trouble trying to add a new column. The following table is an example of the data that I Have:
| Phone | Place | Total Operations | Top visited | Payment | CheckIn |
| 222 | A | 5 | A | 1 | 01/01/20 |
| 222 | A | 5 | A | 2 | 02/01/20 |
| 222 | A | 5 | A | 3 | 03/01/20 |
| 222 | B | 5 | A | 4 | 03/01/20 |
| 222 | C | 5 | A | 4 | 03/01/20 |
| 224 | B | 2 | B | 4 | 03/01/20 |
| 224 | B | 2 | B | 4 | 03/01/20 |
| 225 | B | 1 | B | 4 | 03/01/20 |
I want to know how much a phone user paid in the top place, for example phone "222" has 5 transactions (each row), 3 places visited and the top visited is place "A". If I use:
Payment = Calculate(SUM(Table1[Payment]),ALLEXCEPT(Table1,[Phone]))
I get for user "222" a total payment of 13. But I want the total payment in place A, that is 6.
Thank you.
hi Anonymous
Use this formula to create a new column:
Result = CALCULATE(SUM(Table1[Payment]),FILTER(Table1,Table1[Place]=EARLIER(Table1[Top visited])&&Table1[Phone]=EARLIER(Table1[Phone])))result:
Regards,
Lin
4 Replies
- amitchandak
Super User
Anonymous , I think you are trying to mix two subtotal in one place.
Phone sub total - >Payment = Calculate(SUM(Table1[Payment]),ALLEXCEPT(Table1,[Phone]))
Phone Place Sub Total -> Payment = Calculate(SUM(Table1[Payment]),ALLEXCEPT(Table1,[Phone],[Place]))
In-display if want to switch sub total refer
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
v-lili6-msftCommunity Support
hi Anonymous
Use this formula to create a new column:
Result = CALCULATE(SUM(Table1[Payment]),FILTER(Table1,Table1[Place]=EARLIER(Table1[Top visited])&&Table1[Phone]=EARLIER(Table1[Phone])))result:
Regards,
Lin
HI Anonymous ,
If you want to create a Column.
Top Place1 = SUMX ( FILTER ( ALL ( 'Table'[Phone], 'Table'[Place], 'Table'[Phone], 'Table'[Payment ] ), 'Table'[Place] = 'Table'[Top visited] ), 'Table'[Payment ] )Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
