Pivot Table Subtotals not showing up

Hi all,
I want to create the following pivot table:

I am struggling with obtaining the subtotal for invoices for a given contract.

I tried two different value columns for invoice amount:

invoice amount = min(invoice_amount [invoice_id])
Then I use sum as the aggregation. The values for the invoice amount show up and are correct but the subtotal does not show up. Why does it not show up?

invoice amount = minOver(invoice_amount, [invoice_id],PRE AGG)
The subtotals show up but since I have to use sum as the aggregation for the subtotals to show up, all the values become way too big.

What should I do here? what are the requirements for the subtotals to show up?
Thanks!

Hey Mateoleon,

You don’t need a calculation for the subtotals.


You can just build a Pivot-Table in with a Tabular Layout and rather then hide subtotales for rows it should show you show subtotals for rows.

I hope this can help you.

Best regard
Robert

Hi Robert,
I need to include invoice_id in the calculation in order to deduplicate my data.

Hey,
so I tried to rebuild your problem unsuccessfully and I’m not sure why this calculation:
invoice amount = min(invoice_amount [invoice_id])
doesn’t show you a subtotal. As long as the subtotal is not null it should be shown. Maybe you can rebuild your problem with dummy data and show it in QuickSight.

Hello @mateoleon210, something I noticed the other day when I was handling totals/subtotals in a pivot table, is that an option has been added to aggregate the subtotal/total. Since you table is aggregated down to the invoice level, applying the partition of [invoice_id] in the min field is likely unnecessary. I would try aggregating the subtotal with the partition and without to sum the values returned from each invoice number and it should provide the solution you are expecting. I’ll mark this as the solution, but let me know if you have any remaining questions.