Weighted average on BEx Query Designer

[mauriciobrandon]

Experts,

I have a report requirement where I need to show some KPIs with weighted average. The layout should be like that:

CHAR_1	CHAR_2	CHAR_3	CHAR_4	KPI_A	KPI_B
				KF at cube	KF at cube
Center 1	Lot 1	Material 1	“028”	28	100
Center 1	Lot 1	Material 1	“029”	29	150
Center 1	Lot 1	Material 1	Result	Should show weighted average of KPI_A by KPI_B from white lines = 28,5447	250
Center 1	Lot 1	Material 2	“045”	45	75
Center 1	Lot 1	Material 2	Result	Should show weighted average of KPI_A by KPI_B from white lines = 45	75
Center 1	Lot 1	Result		Should show weighted average of KPI_A by KPI_B from orange lines = 34,3753	325
Center 1	Lot 2	Material 1	“090”	90	250
Center 1	Lot 2	Material 1	Result	Should show weighted average of KPI_A by KPI_B = 90	250
Center 1	Lot 2	Result		Should show weighted average of KPI_A by KPI_B from orange lines = 90	250
Center 1	Result			Should show weighted average of KPI_A by KPI_B from yellow lines = 73,4559	575

I have tried the following: creation of KF that multiplies KPI_A and KPI_B at the cube and use of some calculated KFs at query using SUMCT to divide the lines by the subtotal to help calculate this KPI_A correctly, but the values are wrong:

CHAR_1	CHAR_2	CHAR_3	CHAR_4	KPI_A	KPI_B	KPI_C	KPI_D	KPI_E
				KF at cube	KF at cube	A* B (calculated at cube)	KPI_C / (SUMCT (KPI_A*KPI_B))	KPI_A * KPI_D
Center 1	Lot 1	Material 1	“028”	28	100	2800	0,4553	12,7484
Center 1	Lot 1	Material 1	“029”	29	150	3350	0,5447	15,7963
Center 1	Lot 1	Material 1	Result	57, but should show weighted average of KPI_A by KPI_B from white lines = 28,5447	250	6150	6150 / 9525 = 0,6457	57 * 0,6457 = 36,8049
Center 1	Lot 1	Material 2	“045”	45	75	3375	1	45
Center 1	Lot 1	Material 2	Result	45, but should show weighted average of KPI_A by KPI_B from white lines = 45	75	3375	3375 / 9525 = 0,3543	45 * 0,3543 = 15,9435
Center 1	Lot 1	Result		102, but should show weighted average of KPI_A by KPI_B from orange lines = 34,3753	325	9525	9525 / 32025 = 0,2974	102 * 0,2974 = 30,3348
Center 1	Lot 2	Material 1	“090”	90	250	22500	1	90
Center 1	Lot 2	Material 1	Result	90, but should show weighted average of KPI_A by KPI_B = 90	250	22500	22500 / 22500 = 1,0000	90 * 1,0000 = 90
Center 1	Lot 2	Result		90, but should show weighted average of KPI_A by KPI_B from orange lines = 90	250	22500	22500 / 32025 = 0,7026	90 * 0,7026 = 63,2340
Center 1	Result			192, but should show weighted average of KPI_A by KPI_B from yellow lines = 73,4559	575	32025	32025 / 32025 = 1,0000	192 * 1,0000 = 192

The issue is that the user should be able to add or take characteristics off the report and the report should dinamically calculate this averages and the individual values.

Is there a way to attend this issue the way it is? If not, what could be offered to satisfy the user need?

Regards,

Maurício.

[cornelia_lezoch · Accepted solution]

hi Mauricio,

I would try to find a way with using exception aggregation.

create a calculated key figure (mostly it works better when this is a global one) that does the multiplocation of KPI_A * KPI_B.

Then put an exception aggrgeation to this using the most detailed char, probably char 4 or material. The exception aggregation type should be avereage.

if this still not give the correct results, you might need a sort of nested aggregation - meaning have a calculated key figure with exception aggregation for the first char, then put this into another CKF with exception aggregation for char 2 and so on.

regards

Cornelia

[mauriciobrandon · Accepted solution]

Hi Cornelia,

I've already tried the exception aggregation approach. At the second table from my message, there are KPI_C, KPI_D and KPI_E, if you scroll right the message. KPI_C is configured to use exception aggregation at a created fake characteristic that concatenates all key characteristics.

I'm not sure if there is another thing to complement on this solution. I'm not even sure if this problem does have a solution at BEx.

Regards,

Maurício.