I have a basic Accounts Receivable table with Rows including Invoice, Receipt and Credit Note and one Value Column. While one reference (Invoice) can have many lines due to sometimes multiple receipts and credit notes, I would like to tie all lines back to the original reference (Invoice) and show on one row with separate columns for Invoice, Receipt, Credit Note, Balance (Values)..
I can show this in Power Pivot pretty easily, using a combination of Sumx filter or calculate distinct (I have tried many approaches and get a correct result).
The trouble is when I show the result in Pivot table, the Values are not all on one row. Blank rows keep showing up which makes a “balance” calculation at the end impossible.