Calculate Total Amount using Filter

[Anonymous]

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

[Anonymous · Accepted solution]

Hi @Anonymous ,
Thanks for @Ashish_Mathur  rply.
Here are my measures

Total 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 He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

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.

[Anonymous]

Given the one to many relationship between a property and invoices, how would this work?

[Ashish_Mathur]

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?