Solved

Understanding why pigment sums all the values in a metric ?

  • 13 September 2022
  • 7 replies
  • 497 views

Userlevel 2
Badge +1
  • Seasoned Pigmenteer
  • 22 replies

Dears,

 

I have the following formula:

 

With the following metrics:

 

 and the following structure :

The main di,mension client has mapped a segment as a property and I want to do the calculations by that property (Segment). My two metrics are also in terms of the property; and I want to calculate the the value that I have in MRR BE by Customer for each month divided by the Total Users by each month.

If I go to drill down, I have the following information:

 

Le calcule de MRR BE by Customer and Type is correct as well as the Total Users, but the Final calculation it seems to sum everything again instead of just dividing the two calculations.

 

 

 

Thank you very much in advance for your guidance,

BR

Jose

icon

Best answer by Ben A 15 September 2022, 09:53

View original

7 replies

Userlevel 2
Badge +1

Dears,

 

Do you have an idea of what can I do to solve that problem? For the moment I still have to go to excel to that analysis.

 

Thank you very much in advance,

 

BR

Jose

Userlevel 2
Badge +3

Hello Jose, 

Your metric ARPU by Customer and Type  has two dimensions : Customer & Type .
Segment is only a property (or a parent you may say) of the Customer dimension. 

By default, the ratio in your formula is calculated at leaf level only (customer and type) while you want to display the result at aggregated level (Segment and type). 
As Pigment natively displays aggregations as sums, the results displayed at Segment are wrong (hence your conclusion). You can change this and display an average or other aggregation methiods using the “aggregator” menu, however, none of the options will display what you want. 

So you have two potential solutions: 

  • Create another metric dimensionned by Segment & type and redo your calculation there.
  • Create a table with your 2 metrics (MRR BE by Customer and Type and Total Users). Create a Show value as from the MRR metric and display it as a % of the Total Users metric. That way, the result will be correct at all levels of the hierarchy. 

Hope that helps, 

Ben

 

Userlevel 2
Badge +1

Hello Ben A

 

Thanks for your reply,

 

from the two options I tried the first one before, but the problem is that If I do a metric by segment directly then after I cannot filter by Customer, and I need to remove certain customers from the analysis.

 

For the second Option I tried it, but I arrived at something very different.

what I need to calculate is the sum of  the MRR of every customer with the segment category.XX / the sum of users by the same customers category.XX

 

I need something like the following table, but with the right calculation:

 

 

Thank you very much in advance,

 

Jose

Userlevel 2
Badge +3

Can you please make a screenshot of your table with : 

  • In rows : Customer > Segment  & 3 metrics 
  • Columns: Month
  • Pages: Customers

For the three metrics : 

  1. Total Users
  2. MRR BE by Customer and Type 
  3. MRR BE by Customer and Type (add this one again and do a “show value as” % of Total Users )

That way I will be able to better understand the problem
 

Userlevel 2
Badge +1

Hello Ben

 

Here is the screenshot

 

 

I just want the value of the division of the MRR BE by Customer and total users

 

Thank you very much in advance!

 

BR,

Jose

Userlevel 2
Badge +3

Hi Jose, 

Well it seems to me that this is what you get here.
Example for March 22, Small Segment: 44438 / 6510 = 6.8262 (or 682.62%)

So here maybe there are two things that are off: 

  • If it’s the % then you need to remove the formatting on this metric
  • If in fact what you want is the division with “Total Users” (the sum of total for all segments, and not the total users of each segment) then you can create a “total users” metric that puts the sum of all users on each customer item to achieve this (same process otherwise). 
Userlevel 2
Badge +1

Hello Ben;

 

You are right! I have to change the format and I get the answer!

 

Thank you very much!

 

Jose

Reply