BEx Reporting - "Average" of Key Figures

[Former Member]

Hello Everyone,

In one of our queries, we have 3 columns Pounds, Capacity and (Avg. Pounds)/Capacity.

My query now looks like:

Pounds, Capacity, [Avg(pounds) - Hidden KF],(Avg. Pounds)/Capacity

I have problem calculating the Avg. of Pounds (Hidden column). In the properties of Pounds, I selected 'Calculate Single Value as - Average of all Values'. But, its not working. ***I want to calculate the Average of ALL POUNDS ROWS and then divide this total by capacity of each row.******

Requirement is:

Pounds --- Capacity --- (Avg Pounds)/Capacity

30 100 22/100

20 75 22/75

16 50 22/50

But, even though i select 'Calculate Single Value as - Avg. of all values' or 'Calculate result as - Avg. of all values' in the properties of 'Pounds' KF, i don't get this desired result. It gives me:

Pounds --- Capacity --- (Avg Pounds)/Capacity

30 100 30/100

20 75 25/75

....................................

I understand what its doing. But, i don't want this.

Could some one help me with this problem

Thanks.

[sachin_kulshrestha2 · Accepted solution]

hi,

Create one more column similar to as pound and select the calculate single value as " Normalize result(inc filter)"

this will give you the avg. then you can use formula to divide it.

Hope this helps

sachin k

[Former Member]

Hi Sachin,

I don't want to display the percentages in the report.

Requirement is:

Pounds --- Capacity --- (Avg Pounds)/Capacity

30 100 22/100

20 75 22/75

16 50 22/50

I want to calculate the Average of ALL POUNDS ROWS and then divide this total by capacity of each row as mentioned in the req.

But, If i select 'Calculate Single Value as - Avg. of all values' or 'Calculate result as - Avg. of all values' in the properties of 'Pounds' KF, i don't get this desired result. It gives me:

Pounds --- Capacity --- (Avg Pounds)/Capacity

30 100 30/100

20 75 25/75

Could someone help me.

[Former Member]

Hi,

Any thoughts or ideas?

Thanks.