cancel
Showing results for 
Search instead for 
Did you mean: 

Weighted Average Challenge

mamuewue
8 - Cloud Apps
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:

mamuewue_0-1680509442917.png

In a pivot a present the two months side-by-side in the following way:mamuewue_1-1680509669591.png

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.

mamuewue_2-1680510101234.png

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. 

mamuewue_0-1680510626796.png

Thanks for your support.

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

)

Try this option please.

Kind regards,
Angelina

View solution in original post

mamuewue
8 - Cloud Apps
8 - Cloud Apps

OMG, wonderful. You @Angelina_QBeeQ are my rockstar!

View solution in original post

4 REPLIES 4

Angelina_QBeeQ
10 - ETL
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:

Angelina_1-1680515001396.png

 

Angelina_0-1680514988875.png

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

mamuewue
8 - Cloud Apps
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)? 

mamuewue_0-1680516341675.png

 

 

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
8 - Cloud Apps
8 - Cloud Apps

OMG, wonderful. You @Angelina_QBeeQ are my rockstar!