Forum Discussion
Calculate Total Amount using Filter
I have a table that looks like this
And I want a measure that will calculate two things by Customer Name, so that I can put it in a matrix table.
1. Total Expected Amount: This will calculate the Total Expected Amount by each Customer Name using the data in the Expected Amount column e.g. Customer A should have total Expected Amount = 28,200
2. Total Amount Paid: This will calculate the Total Amount Paid by each Customer Name using the data in Amount Paid column by applying the filter below:
state= Paid & Status = Paid by Customer or Paid by Agency or Paid by BU
OR
Expected Amount = Amount Paid
e.g. Customer F total Amount Paid should be = 138,015
| Customer | Application Title | Invoice Title | Expected Amount | Amount Paid | State | Status |
| A | APP-0000001 | INV-EN-0000001 | 1,000.00 | 1,500.00 | Paid | Paid by Customer |
| A | APP-0000001 | INV-EN-0000002 | 5,000.00 | 5,000.00 | Paid | Refunded |
| A | APP-0000001 | INV-EN-0000003 | 300.00 | |||
| A | APP-0000002 | INV-EN-0000004 | 400.00 | 400.00 | Paid | Paid by Agency |
| A | APP-0000003 | INV-EN-0000005 | 4,000.00 | 4,500.00 | Active | UnderPayment |
| A | APP-0000004 | INV-EN-0000006 | 8,000.00 | 80,000.00 | Active | Overpayment |
| A | APP-0000005 | INV-EN-0000007 | 500.00 | 500.00 | Paid | Paid by Customer |
| A | APP-0000006 | INV-EN-0000008 | 9,000.00 | 9,000.00 | Paid | Paid by Agency |
| B | APP-0000007 | INV-EN-0000009 | ||||
| C | APP-0000008 | INV-EN-0000010 | 200.00 | 200.00 | Paid | Paid by Customer |
| C | APP-0000009 | INV-EN-0000011 | 4,000.00 | 4,000.00 | Paid | Paid by Agency |
| C | APP-0000010 | INV-EN-0000012 | 3,500.00 | 3,500.00 | Paid | Paid by Customer |
| D | APP-0000011 | INV-EN-0000013 | 1,000.00 | 800.00 | Active | UnderPayment |
| E | APP-0000012 | INV-EN-0000014 | 23,000.00 | 23,000.00 | Paid | Paid by Customer |
| F | APP-0000013 | INV-EN-0000015 | 24,500.00 | 20,000.00 | Active | UnderPayment |
| F | APP-0000014 | INV-EN-0000016 | 23,000.00 | 23,000.00 | Paid | Paid by Customer |
| F | APP-0000014 | INV-EN-0000017 | 23,001.00 | 23,001.00 | Paid | Paid by Agency |
| F | APP-0000014 | INV-EN-0000018 | 23,002.00 | 23,002.00 | Paid | Paid by Customer |
| F | APP-0000015 | INV-EN-0000019 | 23,003.00 | 23,003.00 | Paid | Paid by Agency |
| F | APP-0000016 | INV-EN-0000020 | 23,004.00 | 23,004.00 | Paid | Paid By BU |
| F | APP-0000017 | INV-EN-0000021 | 23,005.00 | 23,005.00 | Paid | Paid by Agency |
| G | APP-0000018 | INV-EN-0000022 |
Hi Anonymous ,
Thanks for Ashish_Mathur rply.
Here are my measuresTotal Expected Amount = CALCULATE( SUM('Table'[Expected Amount]), ALLEXCEPT( 'Table', 'Table'[Customer] ) )Total Amount Paid = CALCULATE( SUM('Table'[Amount Paid]), FILTER( ALLEXCEPT( 'Table', 'Table'[Customer] ), 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by Customer" || 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by Agency" || 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by BU" ) )Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi,
Try these measures
Total expected amount = sum(Data[Expected amount])
Total amount paid = calculate(sum(Data[amount paid]),Data[State]="Paid"&&Data[Status]<>"Refunded")
Hope this helps.
You confuse me to the hilt. In the original question, there is no mention of more than 1 table so the question os building a relationship does not arise at all. Furthermore, have you even tried my solution?
Hi Anonymous ,
Thanks for Ashish_Mathur rply.
Here are my measuresTotal Expected Amount = CALCULATE( SUM('Table'[Expected Amount]), ALLEXCEPT( 'Table', 'Table'[Customer] ) )Total Amount Paid = CALCULATE( SUM('Table'[Amount Paid]), FILTER( ALLEXCEPT( 'Table', 'Table'[Customer] ), 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by Customer" || 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by Agency" || 'Table'[State] = "Paid" && 'Table'[Status] = "Paid by BU" ) )Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
