MDX计算问题：求每个Cmd的最大配送时间平均值

Problem Statement #

I have the following fact table:

FK_Cmd | FK_Ship | Delivery_time
C001   | 1       | 20
C001   | 2       | 25
C002   | 1       | 23

I need to calculate the average of the maximum delivery time for each Cmd. In this example, the expected result is (25 + 23) / 2 = 24.

I tried this MDX query but it didn't return the correct result:

AVG(MAX([Dim Cmd].[PR_Cmd],[Measures].[Delivery time]))

Why Your Query Isn't Working #

Your current approach has the function order backwards. The MAX([Dim Cmd].[PR_Cmd],[Measures].[Delivery time]) part calculates the single maximum delivery time across all Cmd records (which is 25 in your dataset), then AVG() just takes the average of that single value—so you end up with 25 instead of the desired 24.

Correct MDX Query #

To get the average of each Cmd's maximum delivery time, you need to:

• Iterate over each individual Cmd member
• For each Cmd, calculate the maximum delivery time across its related Ship records
• Average all those maximum values together

Here's the working MDX:

AVG(
  -- Iterate over every Cmd member in your dimension
  [Dim Cmd].[PR_Cmd].[PR_Cmd].Members,
  -- For each Cmd, get the max Delivery time across all its Ships
  MAX([Dim Ship].[FK_Ship].[FK_Ship].Members, [Measures].[Delivery time])
)

How This Works #

• [Dim Cmd].[PR_Cmd].[PR_Cmd].Members targets all individual Cmd members (C001, C002 in your example)
• For each Cmd, MAX([Dim Ship].[FK_Ship].[FK_Ship].Members, [Measures].[Delivery time]) computes the highest delivery time for that specific Cmd
• AVG() then takes the average of these two maximum values (25 and 23), giving you the correct result of 24.

内容的提问来源于stack exchange，提问作者Mohammed El Mehdi Kherrachi