AR Aging buckets not totaling correctly

[Anonymous]

Hello BI Experts,

I am trying to create an AR Aging report.  I have imported data via Excel.  All amounts come across as a positive value - including credit memos, adjustments, and payments - so I created a measure [AR Aging Amount] to convert those three document types by multiplying by -1.  I then created measures to allocate each items balance into a time bucket of 1-30 day, 31-60 day, 61-90 day and 91+ days aging (based on the age difference between TODAY and the Due Date.  Each line item seems to be correctly converting the value, if appropriate, by multiplying by -1 and also seems to be correctly allocating the amount to the correct time bucket.  However, the totals are not at all correct.  The pbix containing this model can be found here:

AR Aging test.pbix

An easy example to look at is customer number "ENMAX".  Also, if you export the data and sum it in Excel, you will find the actual total's which do not match (e.g. AR Aging Amount).

Can any of you brilliant minds help me resolve the issue?

Thank you,

Stacey

[edhans · Accepted solution]

The total in Power BI doesn't sum column of data. Rather it is repeating your measure for that column but applying it to every record. So:

AR Aging Amount = 
SWITCH(
TRUE(),
MAX('AR Aging'[Document Type]) = "Invoice", [Current Trx Amount *1],
MAX('AR Aging'[Document Type]) = "Credit Memo", [Current Trx Amount *-1],
MAX('AR Aging'[Document Type]) = "Return", [Current Trx Amount *-1],
MAX('AR Aging'[Document Type]) = "Payment", [Current Trx Amount *-1]
)

is being applied to the entire table, and only pulling out a few values to show based on the SWITCH() function.

Instead you should modify the signs in Power Query, but I cannot access your query as I don't have the source. If you added a calculated column with the following formula:

Amount = 
IF(
    [Document Type]="Return" || [Document Type]="Credit Memo" || [Document Type] = "Payment",
    'AR Aging'[Current Trx Amount] * -1,
    'AR Aging'[Current Trx Amount]
)

This will change the signs. Again, Power Query is better, but this works for this example.

Then create a new measure called "Total Amount" which is just:

Total Amount = SUM('AR Aging'[Amount])

Then you will get the right calculation all the way down.

[Anonymous]

@edhansI just realized it's giving the correct grand total amount, but the totals are still not correct for the time buckets (1-30, 31-60, 61-90, 91+).  The updated file is here:  AR Aging test v2

[Anonymous]

Aha!  I realize now I needed to also create the age buckets via Power Query, as well.  Now that I've done that they each sum correctly in my report.

---

[Anonymous]

That did it!  Thank you so much for your help!