cancel
Showing results forย
Did you mean:ย

# Weighted Average Challenge

8 - Cloud Apps

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.

2 ACCEPTED SOLUTIONS

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]
)

)``````

Kind regards,
Angelina

8 - Cloud Apps

OMG, wonderful. You @Angelina_QBeeQ are my rockstar!

4 REPLIES 4
10 - ETL

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
angelina.bulgakova@qbeeq.io
QBeeQ  - Gold Implementation and Development Partner

8 - Cloud Apps

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)?

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]
)

)``````

Kind regards,
Angelina

8 - Cloud Apps

OMG, wonderful. You @Angelina_QBeeQ are my rockstar!

Community Toolbox

Developers Group:

Product Feedback Forum:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email community@sisense.com