Dear All,
If I have a simple formula as;
ADB=Sum(NetRevenue) / Sum(AdultNight) which is €70.48 = €7,646,735.88 / 108,498 in this data set.
How will I calculate the Cumulative Weighted Mean of ADB (weight=AdultNight) by Sales Date to visualise "ADB Cumulative Mean vs. Sales Date"
The formula I know can not calculate the weight which puts out the wrong final ADB as €68,51
Tables attached
Many thanks in advance for your help.
Does this formula return the numbers you want?
Sum(:Name("Sum(NetRevenue)")[1 :: Row()]) / Sum(:Name("Sum(AdultNight)")[1 :: Row()])
Does this formula return the numbers you want?
Sum(:Name("Sum(NetRevenue)")[1 :: Row()]) / Sum(:Name("Sum(AdultNight)")[1 :: Row()])
Yes it does! Thank you.
Is formula
Sum(:Name("Sum(NetRevenue)")[1 :: Row()])
a way of calculating the Cumulative Sum?
If I would change the Sum with Mean would it calculate the Cumulative Mean?
Yes.
Mean(:ADB[1::Row()])
should give the same result as your formula
If(Row() == 1,
:ADB,
(:ADB Cumulative Mean[Row() - 1] * (Row() - 1) + :ADB) / Row()
)
However, for very large tables your formula would probably be significantly faster, despite the conditional structure, because it does not require extracting a matrix for each row.