ConsolidatedAvg with several dimensions

Post Reply
ygrodrig6
Posts: 1
Joined: Thu Jul 28, 2016 8:55 pm
OLAP Product: Cognos TM1
Version: 10.2.2
Excel Version: 2007

ConsolidatedAvg with several dimensions

Post by ygrodrig6 »

Hello everyone! This is my first post and it is perhaps a little silly.

Anyways, I'm trying to use ConsolidatedAvg in a cube that has several dimensions (see the attachment). Among its dimensions, I have month and client. I am at the consolidated level of client and month (year), and in the rest of dimensions I am at leaf level. My rule is like this:

['Total of AlzCliente', 'Total Año', '% Peso'] =
ConsolidatedAvg(2, 'AlzParticipacionCliente', !AlzAnio, 'Total of AlzCliente', !AlzMes, !AlzAlianza, !AlzProveedor, !AlzCanal, '% Peso');

The cube is as follows:
HelpTM1.jpg
HelpTM1.jpg (115.42 KiB) Viewed 2694 times
I'm trying to get the average of a measure called %Peso with the value in each month but for some reason it calculates the average of the clients. I am getting a 50 instead of the 100 that I want to get.

Do you have any idea what I might be doing wrong? Thank you for your help.
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: ConsolidatedAvg with several dimensions

Post by Duncan P »

ConsolidatedAvg gives you the average of all leaf cells below the one you have chosen with the coordinates.

It appears that you want the average of the sums across client. You should be able to get this simply by multiplying each result by the number of leaf clients feeding into it. You can get that number by having an attribute of the client dimension that you keep maintained, or by having a one dimensional cube, by client, with ones at leaf level and just reading off the consolidated values.

I hope I've understood your problem correctly and that my knowledge is not too rusty.
BariAbdul
Regular Participant
Posts: 424
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: IBM TM1, Planning Analytics, P
Version: PAW 2.0.8
Excel Version: 2019

Re: ConsolidatedAvg with several dimensions

Post by BariAbdul »

Hi ygrodrig6,How did you go by going through Duncan P suggestion? Thanks
"You Never Fail Until You Stop Trying......"
Post Reply