calculating expression where dimensions have a many to many relationship

[#1]

Hello,

I need a hand with an expression where where the dimensions have a many to many relationship and a twist...

The dimensions are Player, Machine, Hold (%) as well as Turnover ($).  I need to calculate how much we keep (Hold %) of the Turnover for each player

     -     Each player has Turnover on multiple machines

     -     Each machine has an individual Hold %.

So what I am having trouble calculating is the total $ Spend per Player

Player          Machine          Hold           Turnover             Turnover * Hold

A B               1                    .12               100                    12.00

A B               2                    .11               110                    12.10

A B               3                    .13               120                    15.60

TOTAL $ Spend for A B = 39.70 - how do I get this value?

Thanks in advance,

Julian

[jagan · Accepted solution]

Or else try this expression

=Sum(Aggr(Avg(Hold) * Sum(Turnover), Player, Machine, Hold, Turnover))

Hope this helps you.

Regards,

Jagan.

[#3]

Thanks for all your help Jagan.  It looks like both solutions are going to work.  The aggr is an instant fix and in my test script the load solution looks like it is going to work as well.  I will just have to have a play to get it right.  You are right though.  It will be much better to get it in the script.