Weighted Average Challenge

[mamuewue]

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.

[Angelina_QBeeQ · Accepted solution]

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,
Angelina

[Angelina_QBeeQ]

Hi @mamuewue ,

In Sisense, you can set the rules for calculating the values in the grand total.
By default, it is calculated by the original formula. But you can set it as a sum of values in the table.
Here is the example:

Always here to help,
Angelina from QBeeQ
[email protected]
QBeeQ  - Gold Implementation and Development Partner

[mamuewue]

Hi Angelina, 

this is very helpful as a first step. I can can compute the number now in the table where I have all customers listed (picture marker 2). However, is there also any way to make the formular "Weighted Average" in the above pivot (picture marker 1), where no customers are listed (and just the sum)? 

[Angelina_QBeeQ · Accepted solution]

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,
Angelina

[mamuewue · Accepted solution]

OMG, wonderful. You @Angelina_QBeeQ are my rockstar!