Calculated Key figure Complicated

[Former Member]

Experts,

I am struggling with the design of a report which requires the following fields

Payer ShipTo PH2 Year Units Price Sales $ Price input

HD 1234 1301 2006 16 $19.00 $304.00 $19.00

Lowes 9999 1301 2006 100 $19.60 $1,960.00 $19.60

Lowes 9999 1303 2006 300 $29.00 $8,700.00 $29.00

HD 1234 1303 2006 200 $32.00 $6,400.00 $32.00

Above is what is directely coming for the cube. I need to calculate "Base Price" which is the average of "sales" by PH2. for example the report will like like following

Payer ShipTo PH2 Year Units Price Sales $ Price input Base_Price

HD 1234 1301 2006 16 $19.00 $304.00 $19.00 1132 Sales Avg by PH2

Lowes 9999 1301 2006 100 $19.60 $1,960.00 $19.60 1132 Sales Avg by PH2

Lowes 9999 1303 2006 300 $29.00 $8,700.00 $29.00 7550 Sales Avg by PH2

HD 1234 1303 2006 200 $32.00 $6,400.00 $32.00 7550 Sales Avg by PH2

Any thoughts on how I can do this...

Help will be greatly appreciated

Thanks

Raj

[former_member207028 · Accepted solution]

Hi Yashu,

Create Calculated Key figure on sales, and select properties od that CKF and on Exception aggregation select Average of all values <> 0, and exception chareteristcis as PH2.

hope this helps

Regards,

Daya Sagar