Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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