Skip to main content
Solved

How to take the average division (and not a sum) in a metric when you change the pivot ?

  • 17 November 2022
  • 2 replies
  • 558 views

 We are trying to make the average of all the elements of the country dimension (in rows). 
When we change the pivot country from rows to pages, it makes the sum of the lines and not the average. 
Thank you in advance for the help and the contribution to the pigment community ;))

 

Best answer by francois

Hi,

To show an average on all levels of your pivot, the best is to show the two source absolute metrics (in that case I would guess it to be Total sales per country and Volume per country), add them in a table and use the Show Value As feature to display the Total sales per country as a % of Volume per country.

In this example I’m doing Gross Margin / Example Revenue

To show the relevant format in your case you’ll have to edit the number formatting in the Format tab.

You can then hide the Volume per country if you want.
This will show a ratio on all the aggregations, which should work in your case.

 

The reason with this is that Pigment only calculates the value in your metric at the most granular level (the structure of your metric), and not at aggregated levels (which you chose in your Pivot). Using a Show Value As ensures you have the absolute data at the most granular level and ratios are then calculated whenever you aggregate data.

View original

francois
Employee
Forum|alt.badge.img+14
  • Employee
  • November 17, 2022

Hi,

To show an average on all levels of your pivot, the best is to show the two source absolute metrics (in that case I would guess it to be Total sales per country and Volume per country), add them in a table and use the Show Value As feature to display the Total sales per country as a % of Volume per country.

In this example I’m doing Gross Margin / Example Revenue

To show the relevant format in your case you’ll have to edit the number formatting in the Format tab.

You can then hide the Volume per country if you want.
This will show a ratio on all the aggregations, which should work in your case.

 

The reason with this is that Pigment only calculates the value in your metric at the most granular level (the structure of your metric), and not at aggregated levels (which you chose in your Pivot). Using a Show Value As ensures you have the absolute data at the most granular level and ratios are then calculated whenever you aggregate data.


Nathan
Employee
Forum|alt.badge.img+12
  • Employee
  • November 18, 2022

Before going down François’ road, there is the native total aggregation of dimensions that you can set to what you need in the totals panel. find the doc here:

Setting up Totals in the Aggregator Panel | Community (gopigment.com)

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings