5 Replies Latest reply: Jun 17, 2015 11:40 AM by David Rodriguez

# Different Total calculation in Pivot Table

Hi all!!

I have a pivot table and i need different total calculation depending the dimension.

This is the expression:

=AVG(TOTAL <MONTH_SHORT_NAME, HAUL> Aggr(SUM({<MIS_KPI_ID = {'AVO_PROMEDIO'}>} VALOR_KPI),MONTH_SHORT_NAME, HAUL))

The total of HAUL (horizontal row) is an average, but the total of MONTH SHORT NAME (vertical column) is a SUM expression. How can i do this expression to make differents total calculations?

Best Regards.

• ###### Re: Different Total calculation in Pivot Table

maybe with dimensionality() and secondarydimensionality()

How to use - Dimensionality()

• ###### Re: Different Total calculation in Pivot Table

The simple version.

If(SecondaryDimensionality()=0,

SUM(HAUL), //If total, then sum

AVG(HAUL) //else avg

)

//Martin

• ###### Re: Different Total calculation in Pivot Table

Thanks martin.dideriksen the solution works fine, but i have a problem with the aggregations (for more detail see attachment).

When i have all the dimensions expanded the total is correct:

But if i collapse the dimensions, the total is wrong:

I'm not able to solve the aggr expression for totals. This is my expression:

If(secondaryDimensionality()=0,

Avg(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI>           Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI)),

Sum(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI>          Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))

)

How can I modify this expression?

Thanks for help me!!

• ###### Re: Different Total calculation in Pivot Table

The solution to fix the expression has given me hic :

If(secondaryDimensionality()=0,

SUM(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI>          Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))/COUNT( distinct MONTH_SHORT_NAME ),

Sum(TOTAL <MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI>          Aggr(Sum(NUM_VALUE),MONTH_SHORT_NAME,ORIGEN_KPI,SECTOR_KPI,CIA_KPI,ID_AREANEG_KPI))

)

Thanks all for help me!

• ###### Re: Different Total calculation in Pivot Table

Wouldn't the following work?

SUM({<MIS_KPI_ID = {'AVO_PROMEDIO'}>} VALOR_KPI) / COUNT( distinct MONTH_SHORT_NAME )

Within a column, there is only one month, which means that you get the Sum. But in a row, there are several months, so you get the average.

HIC