# I can't seem to use an 'avg' aggregation

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # I can't seem to use an 'avg' aggregation

I want one of the measures in my cube to be averaged, not summed or counted. 'sum' and 'count' work just fine; I have measures aggregated in both ways. However, the measure for which I selected 'avg' as the aggregation type still displays the sum. What might I be doing wrong? I'm attaching screen shots to help pinpoint the issue.

As an example, in the "Output.png" screenshot, I would expect the DaysOpen value for 12-009876 (about halfway down) to be 3, since each of the two lines under it is 3. I've also attached screen shots of the tree extract listing the measures available in this cube and the table transform where the DaysOpen measure is defined.
Images
• TreeExtract.png

9.59 kB, 685×237, viewed 366 times
• TableTransform.png

24.01 kB, 768×471, viewed 382 times
• Output.png

5.7 kB, 245×320, viewed 351 times
• Hmm... Now that I'm thinking of it, a rule of ['Avg Days Open'] = ['DaysOpen'] / ['Count'] would do it. I still don't understand how the 'avg' aggregation works, but that solves the immediate issue.
• Hi,
Currently OLAP doesn't support other aggregation functions than sum as cell results.
Your solution using rules is correct.
Other option is that you can replace PALO.DATA by PALO.DATA.AVG spreadsheet formula. doc: knowledgebase.jedox.com/knowle…jedox-palo-olap-functions
It can be quite complicated and slower at the end, because it is quite flexible. You can calculate averages over selected dimensions and over one or more aggregation levels.

Jiri
• This "avg" aggregation in ETL defines how the source data of the TableTransform is aggregated before it is written to Olap server.
It does not define how aggregation from base to consolidated cells is done in Olap analysis.