Change Total Formula

[micheledenardi]

Dear all,

starting from the sample data attached, in Qlik Sense i want to create a table with a simple average of quantity per product and in the total the summary of all averages (not the grand average).

So i would like to specify one formula for the details and another one for totals. is it possible?

Below the result i have to achieve.

[tomovangel · Accepted solution]

Hello Micheledenardi ,

I am attaching the Qlik Sense application with needed result. 

the way is to get Product in a straight table(as dimension) and use the following measure.

sum(aggr(sum(Quantity)/count(Quantity),Product))

[micheledenardi]

Thanks Tomovangel,

your solution works on straight table.

What if i want to use the same login on a pivot table where the aggr() level depends from the table dimensions expansion?

If the pivot table is completelly compressed i have to apply an aggr() by the first dimension, if the table has the first two dimension expanded i have to apply another aggr() by the firsts two dimension and so on... i have to setup something similar to a dynamic aggr() funcion.

I don't want to set my pivot table always fully expanded.

[micheledenardi]

Solved by myself using SecondaryDimensionality().

So

if(SecondaryDimensionality()=1,    //Pivot table is completelly collapsed

sum(aggr(sum(Quantity)/count(Quantity),Product)),

if(SecondaryDimensionality()=2,  //Pivot table has 1st dimension expanded

sum(aggr(sum(Quantity)/count(Quantity),ProductCategory,Product))

))