The purpose of this article is to explain how you can calculate ratios in Pigment across all summary levels
Consider the following example, we have Cost and No. of Units by Country and Month. We want to calculate Cost per Unit. To calculate this ratio, you would normally do:
Costs / No. of Units = Cost per Unit
When we enter this formula in Pigment, we can see the Cost per Unit at a country level, but, the total for the region calculates incorrectly. Pigment shows 230, but the correct answer is 165,000 / 1500 = .110
Pigment is calculating the ratio correctly at the lowest level of granularity, which is the level that the metric is dimensioned by. The summary levels are calculated using the summary method of the block (default total is to sum the calculated ratios, 100 + 130)
So how can we calculate Cost per Unit correctly at the summary levels?
Option 1 - One metric per level of granularity 🍦
Create one metric to calculate the ratio at a Country Level. Create one metric to calculate at a Region Level.
Pros - Easy to learn and easy to create
Cons - It's annoying to create multiple metrics that calculate the same ratio
How to set this up
Step 1: Create 'Costs per Unit (Country)' dimensioned by Month and Country. Formula is No. of Units / Costs
Step 2: Create 'Costs per Unit (Region)' dimensioned by Month and Country. Formula is No. of Units / Costs
Step 3: Create saved views and publish to dashboards
Option 2 - Show Value As / Calculated items [Recommended] 🍕
The second and recommended option leverages ‘Show Value As’ or ‘Calculated Items’.
Pros - The most powerful and flexible solution
Cons - More time consuming to set up
How to set this up
Step 1: Ensure the numerator and denominator of the ratio you are calculating are in two separate metrics
Step 2: Add both metrics to a table
Step 3: Click 'add this metric again' on the numerator metric. Rename the metric to the name of your ratio
Step 4: Right click on the ratio, press 'show value as', % of, another metric, choose denominator
Step 5: Change formatting to be a number instead of a %