Customize matrix subtotals

[Anonymous]

Hi there!

I'm currently struggling with a report that should calculate the bonuses for our Salesteam. This bonus consists of 3 items (Bonus UNI, Bonus UNF & Bonus PA). These 3 items are summed, which gives us the 'Bonus Total' (i.e. Bonus Algemeen Totaal (voor eval.) - see printscreen). Then our Sales manager decides to multiply the 'Bonus Total' by x0,9, x1 or x1,1 (depending on the fact if my colleagues did a good job or not during that week). 

This gives us the 'Final Bonus Total' (i.e. Bonus Algemeen Totaal (na eval.) - see printscreen. 

Unfortunately, I'm having some trouble to summarize the 'Final Bonus Total' per week (= sum of 'Final Bonus Total' for all colleagues). 

1. Desired result: 

2. Datamodel. 

See printscreen. 

3. What I have done so far:

I made the following measures:

- Bonus UNI Total

- Bonus UNF Total

- Bonus PA Total

- Bonus ALGEMEEN TOTAAL (voor eval.), which is the sum of the 3 measures above: 

Bonus ALGEMEEN TOTAAL (voor eval.) =

'Metingen objectieven & bonus'[Bonus UNI TOTAL]+'Metingen objectieven & bonus'[Bonus UNF TOTAL]+'Metingen objectieven & bonus'[Bonus PA TOTAL]

- Multiplicator: as the multiplicator is decided for each colleague seperately, I decided to make the multiplicator-value on subtotal-level = 0

Multiplicator =
IF (
    ISINSCOPE ( 'TUSSENTABEL COLLEGA''s'[Naam collega] ),
    CALCULATE ( AVERAGE ( BONUSE[Multiplicator evaluatie] ) ),
    0
)

- Bonus ALGEMEEN TOTAAL (na eval.): this is currently the issue... I don't get any further than this (in which the '0' should probably be replaced by another expression):

Bonus ALGEMEEN TOTAAL (na eval.) =

VAR BonusnaEval = [Bonus ALGEMEEN TOTAAL (voor eval.)]*'Metingen objectieven & bonus'[Multiplicator]

RETURN

if(ISINSCOPE('TUSSENTABEL COLLEGA''s'[Naam collega]), BonusnaEval, 0)

MANY THANKS FOR YOUR HELP! 

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create another measure like so:

Bonus ALGEMEEN TOTAAL (na eval.) 2 =
SUMX (
    VALUES ( 'TUSSENTABEL COLLEGA''s'[Naam collega] ),
    [Bonus ALGEMEEN TOTAAL (na eval.)]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.