Calculating the total value per customer - maybe using SUMX?

DAX DAX Calculations
1

Hello all,

Looking for any help in figuring out how I can re-create the Total Open Amount results using DAX formula.

sumx
sumx.png914×215 4.86 KB

I’m quite new in using DAX so your help is much appreciated!

Cheers,
Kim

2

@KimDay,
You can use the following in a new calculated column:

Total Open Amount = 
var __CurrentID = Table1[CustomerID]
VAR __CurrentInvoiceID = Table1[InvoiceID]

RETURN

CALCULATE(
    SUM( Table1[Invoice Amt] ),
    FILTER(
        Table1,
        Table1[CustomerID] = __CurrentID
    )
)
-
CALCULATE(
    SUM( Table1[Paid] ),
    FILTER(
        Table1,
        Table1[CustomerID] = __CurrentID
        && Table1[InvoiceID] <= __CurrentInvoiceID
    )
)

The first calculate is generating the grand total of the invoice amount by Customer ID.

The second calculate accounts for amounts paid. I went on the assumption that the invoiceID column is in order, meaning the later the Invoice ID the later that invoice took place. So if you look a this example, I added $50 to paid for customer 2 on their second invoice. And that 50 is accounted for when paid, and then total open amount after that is the 600 - that 50.

You can do the same type of thing using measures for sure. But that would require more information and more setting up of the data model and such.

Running%20Total
Running%20Total575×642 18.2 KB

Enterprise%20DNA%20Expert%20-%20Small

3

Another idea would be this as well if you wanted this in a measure, which I recommend

Try using this formula:

=CALCULATE( SUM( [Open Amount] ), ALL( Data[CustomerID] ) )

See if this gets you what you need as well.

Thanks
Sam