Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Customize matrix subtotals

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! 
Need Help
  • Icey's avatar
    Icey
    4 years ago

    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.

1 Reply

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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.