Getting sum subtotals after using min as the level of aggregation

Question & Answer
1

hi, I want to get the subtotals of my data using a pivot table after selecting min as the level of aggregation.
However, whenever I do this, the subtotals become 0.

For example, on the table below, I want the subtotals for A and B but before I can do that, I need to get rid of the duplicate row that contains Spain so I selected min as the level of aggregation for price (if I choose sum the value for Spain becomes $100 which is wrong). Selecting min however does not let me get the right type of subtotal whenever using the pivot table.

Letter. - Country. -Price
A. - England - $20
A. - France - $30
A. - Spain - $50
A. - Spain - $50
B. - USA - $20
B. - India - $30

Do you have any ideas on how I could get the subtotals but at the same time skip the duplicates?
My final goal is to get: A - $100 and B - $50

2

Hello @mateoleon210 This can be achieved leveraging Level Aware Calculation. You need to create a new calculated field using the below calculation.

sumover(sum(min(Price,[Country, Letter])),[Country, Letter])

Please find detailed snapshots below for reference. Hope this helps!

image
image1700Ă—625 38.5 KB

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you

2 Likes
3

Thank you so much for taking the time. This is perfect.

2 Likes