Aggregates to show averages instead of totals

[former_member196865]

Hi Gurus,

We have a report where we want to show the average of the accounts instead of the totals at the top most level. I am wondering if there is a way to achieve this.

We are loading data for 3 accounts with each one of them having a different format.

For Example:

                         Account1          Account2(Rate)      Account3

2016 Jan          3%                    34                              2000

2016 Feb          5%                     35                             10000    

2016 Total          4%                   34.5                             12000

Can something be done at the account member level i.e. change a property to show the type of account it is?

Regards,

David

[former_member186338 · Accepted solution]

Hi David,

Please read  the concept here

In most cases generic average will be meaningless, you will need a weighted average like in my sample.

Vadim

[former_member186338 · Accepted solution]

P.S. Sample - if you have discount percent then simple average will show useless figures

[former_member196865 · Accepted solution]

Thanks Vadim.

The user requirement is not as complicated as your example, but I get your point. It is better to write a formula or SL for it. I am thinking of something like

If Time period is "2016 Total" then divide the number by the baseperiod of last month with data.

Correct me if I am wrong.

[former_member186338 · Accepted solution]

Ffor what type of value do you want to perform this calculation? Please explain in details...

it,s meaningless to do it for percent or rate...

[former_member186338 · Accepted solution]

P.S. "last month with data" - is also incorrect idea

[former_member196865 · Accepted solution]

Hmm, the requirement is that the user needs to see the averages of the current year when the Total member for the year is picked.

Say Account 1 has percentages maintained for the current year, when he/she pulls "2016 Total" into the report, he/she should see the average of the values instead of the total values. That is if there is data for 6 months and the the user pulls "2016 Total" member for this particular account he/she needs to see the average for the six months. For all other accounts he needs to see the totals and not averages.

It is done so as to not confuse the users when they see the sum of the percentages when "2016 total" is pulled than anything else. (If Jan 2016 has 48% and Feb 2016 has 64% then if it shows as 112% for 2016 Total, the other users will get confused). It is not used for any other calculations just more of a dummy representation.

[former_member186338 · Accepted solution]

Ssorry but average for percentage in most cases is the same meaningless figure as sum of percentages. It's better to show simply nothing in this case then average!

Another thing you have to understand that the system has no idea that only 6 month are available. I for some month you have no record the system assumes it as zero record! Sample:

for  some account you have 6 month data, but the last month have correct zero value. What will be your last month

[former_member186338 · Accepted solution]

P.S. Sorry, I am writing on iPad and this forum is absolutely not iPad friendly...

[former_member196865 · Accepted solution]

Hmm..I get your point it is meaningless value. If the system considers all time members regardless of data then it is better to have nothing there. Is there a way to show nothing for Totals for a particular account?

[former_member186338 · Accepted solution]

Just create a custom measure formula and using some property select accounts to show nothing on parents (if someproperty = y and CALC = y then Null) for the rest - use standard measure text...

[Shrikant_Jadhav]

Hi David,

You can achieve this by two different ways,

1) Create local member and put Average formula

2) Create custom measure

Shri

[former_member196865]

Hmm... I know that creating a local member can achieve this but the users create ad hoc reports all the time and I was wondering if we can do something at the back end to make it easy for them when they pull the data.