Period End Average Selling Price

By Nic | December 4, 2025

To calculate an average price in the Year_Total/Total_Period member is quite straight forwards if you’ve got your period hierarchy as dynamic calc. It just needs a forumula.

It doesn’t need to be a Two Pass calculation member.

The ASP (Average Selling Price) member in my model has the following properties:

The time balance and account type aren’t really relevant as the formula overrides this functionality. As it is a price, it is ignored in the dimension roll ups. TRAVPLAN is not used.

In the above grid the ASP is 967 = Revenue 625,888,722 / PAX 647,000

Whereas the mean of the 6 prices is 1015.

Formula

The formula in the Total_Period for ASP has two cases depending on the Step Member. All other accounts are a sum of the periods in this model.

IF(@ISMBR("ASP") )
  IF(@ISMBR("Step 2 Calculated","Step2","Step 3 Calculated","Step3","Step 4 Calculated","Step4","Step 5 Calculated","Step5","Step 6 Calculated","Step6","Step 7 Calculated","Step7","Step 8 Calculated","Step8"))
    "MCT_Revenue" / "PAX";
  ELSE
    @SUM("Jan":"Dec")/12;
  ENDIF
ELSE
  @SUM("Jan":"Dec");
ENDIF

Posted in Tips and tagged , ,

Leave a Comment