Issue
My MDX, below, does not return the average gross profit number I expect.
MDX
select NON EMPTY {Hierarchize({[New Repeat].[New Repeat].[New]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Measures].[Order Count], [Measures].[Total Gross Profit], [Measures].[Average Gross Profit]})} ON ROWS
from [USAOrders]
where {({{[Voucher Code].[ABC], [Voucher Code].[DEF]}})}
Returns:
New Repeat = New
Order Count = 13
Total Gross Profit = 438.03
Average Gross Profit = 24.668
(Average Gross Profit should be 438.03/13 = 33.69)
How the number is derived
The SQL derived from this MDX returns 2 rows:
new repeat voucher_code | order_count | total_gross_profit | avg_gross_profit
"ABC" 2 23.26 11.63
"DEF" 11 414.77 37.7063636363636
So, looking at the numbers it looks like the MDX works out the Average Gross Profit for all specified vouchers by adding up the Average Gross Profit for each individual voucher and then dividing by the number rows of different vouchers (in this case 2):
e.g. (11.63 + 37.7063636363636)/2 = 24.668
Whereas, in order to get the correct number, it should add up the values in Total Gross Profit and divide by the number of rows from the original data:
e.g. (23.26 + 414.77)/13 = 33.69
(Within the cube, Total and Average Gross Profit are measures defined using the sum and avg functions on the gross profit measure/column).
Is there a way to resolve the issue to get the correct number?
Many thanks for any suggestions.
