Different Total calculation in Pivot Table

[#1]

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.

[maxgro]

maybe with dimensionality() and secondarydimensionality()

How to use - Dimensionality()

[martin_dideriks]

The simple version.

If(SecondaryDimensionality()=0,

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

     AVG(HAUL) //else avg

)

//Martin

[hic]

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

[#5 · Accepted solution]

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!!

[#6]

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!