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

  • 17 November 2022
  • 2 replies

 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 17 November 2022, 17:32

View original

2 replies

Userlevel 6
Badge +14


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.

Userlevel 6
Badge +11

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 (