Skip to main content

The purpose of this article is to explain how you can calculate ratios in Pigment across all summary levels


Context


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 sRecommended] 🍕
 

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 %

Is there a way to modify or add additional calculations to the “show as”? I have used the second method (shown above) and now have a percent value that = Churn/Revenue (eg -1/10 =-10%). However, what I really want to get is (1+Churn)/Revenue (eg 9/10 = 90%). I would also like to have this across all the dimensions that are currently applied to Churn and Revenue.

@Josh  - Great Question - instead of doing “% Of”, use “Growth from”
 

 

Hi @Elliot, In the example you gave the numbers are different from what I was trying to achieve. In your example above, revenue should be 10 and churn should be -1.

Hi! going back to this topic. Has there been any development in here? I am successful in showing Gross Profit Margin (Gross Profit / Revenue) as show value as. In addition to that I would like to reference this and show simple change month over month, but I am not able to reference the previous ratio. Is there any way I can do this?

Hello there, I recommend tracking this idea for the most up to date news about this feature: 

 

Reply


Ask your question here!

Terms & ConditionsCookie settings