Weighted Average Challenge
Hi Community,
I'm trying to resolve a weighted average challenge, but i can't get to it. Hoping there is help out there.
My table has data as follows:
In a pivot a present the two months side-by-side in the following way:
Additionally to the side-by-side data, I want to calculate how much revenue I potentially could have gotten. Number of devices typically increase, but the MRC (Monthly Recurring Revenue) doesn't necessarily increase in the same scale. Usually price discounts are given with more devices added.
So I want to calculate the "Expected_new_Revenue". I do this by multiplying the ARPU (Average Revenue Per Unit) of the previous month (January) with the Devices of the current month (Feb). From this "Expected_new_Revenue" I now want to derive my "Delta" missing revenue; aka: "This month revenue" - "Expected_new_Revenue".
To make this more clear, here is an excel example. Sisense today presents me the 1,305.68 result; but what I need is the 592.71 as result.
My formular for the "Expected_new_Revenue" is:
((
([Total MRC], PREV([Months in Revenue_Date],1))
/
([Total Devices], PREV([Months in Revenue_Date],1))
)
* [Total Devices]
)
And the formular for the "Delta" (missing revenue):
[Total MRC] -
((
([Total MRC], PREV([Months in Revenue_Date],1))
/
([Total Devices], PREV([Months in Revenue_Date],1))
)
* [Total Devices]
)
Even if I add the "Customer" in the pivot table, and it shows the right "subvalues" per row, the Grand Total still is the wrong compute.
Thanks for your support.
In this case you need multi-pass aggregation.
You manually set the "Customer" group in the formula and summarize the results.Sum( [Customer], [Total MRC] - (( ([Total MRC], PREV([Months in Revenue_Date],1)) / ([Total Devices], PREV([Months in Revenue_Date],1)) ) * [Total Devices] ) )Try this option please.
Kind regards,
AngelinaOMG, wonderful. You Angelina_QBeeQ are my rockstar!
