How can I create a formula which will calculate per unit calcs correctly along all levels of a hierarchy. For example if at the top of the Product hierarchy calc Sales for all products/ Units for all products. It looks like Pigment adds the sum of all Sales/Units prices.
Solved
Sales per Unit Pricing along a hierarchy
+1- Author
Best answer by KeeganSF
Hi Kevin!
This is a common issue, by default, when you have a formula like Sales / Units, Pigment aggregates the result — so at parent levels in your hierarchy, it sums the individual per-unit prices instead of computing Total Sales / Total Units.
A potential solution is to use Show Value As
Instead of a single ratio metric, keep Sales and Units as separate metrics in your table, then:
- Click on the Sales metric → Show Value As → % of → select the Units metric
- Hide the Units metric in the pivot
- Change the formatting to absolute (not percentage)
This way the division happens after aggregation, giving you the correct ratio at every hierarchy level.
Here's a community article walking through this approach: How to Calculate Ratios and Averages at Summary Levels
Hope that helps!
+3- Employee
- Answer
Hi Kevin!
This is a common issue, by default, when you have a formula like Sales / Units, Pigment aggregates the result — so at parent levels in your hierarchy, it sums the individual per-unit prices instead of computing Total Sales / Total Units.
A potential solution is to use Show Value As
Instead of a single ratio metric, keep Sales and Units as separate metrics in your table, then:
- Click on the Sales metric → Show Value As → % of → select the Units metric
- Hide the Units metric in the pivot
- Change the formatting to absolute (not percentage)
This way the division happens after aggregation, giving you the correct ratio at every hierarchy level.
Here's a community article walking through this approach: How to Calculate Ratios and Averages at Summary Levels
Hope that helps!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.