Forum Discussion

calerof's avatar
calerof
Impactful Individual
6 years ago
Solved

Incorrect total when using variables

I need to calculate commissions for the sales representatives based on two variables, sales amount and profit margin, as per the instructions table below:     For this calculation I'm using ...
Need Help
  • calerof's avatar
    calerof
    6 years ago

    Hi TomMartens,

     

    The commission is based on total sales by territory, not sales order.

     

    I looked again more carefully at your second reply and tryed this change in my measure:

     

     

    Commission 2= 
SUMX(
VALUES( OTER[Territory] ),
VAR Sales = [Sales Total]
VAR Margin = [Margin Total]
VAR Commission = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 0,
            Sales >= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
            Sales >= 250000 && Margin < 0.60, 0.015,
            Sales >= 250000 && Margin >= 0.6, 0.02,
            0
        )
VAR BaseBonus = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 1000,
            0
        )
VAR CalcComision = Sales * Commission + BaseBonus
RETURN
CalcComision
)

     

     

    And it worked! ðŸ¤“

     

     

    Simple is best!

     

    Thank you very much for your help and the push to continue thinking on the best way to work around here with PBI.

     

    Fernando

     

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey calerof 

 

here I try to rewrite the measure as I suggested:

Commission = 
SUMX(
    VALUES( OTER[Territory] )
    ,
    VAR Sales = [Sales Total]
    VAR Margin = [Margin Total]
    VAR Commission = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 0,
            Sales >= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
            Sales >= 250000 && Margin < 0.60, 0.015,
            Sales >= 250000 && Margin >= 0.6, 0.02,
            0
        )
    VAR BaseBonus = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 1000,
            0
        )
    RETURN
    MAX( 0, Sales * Commission + BaseBonus )) 
    --SUMMARIZE( VALUES( OTER[Territory] ), OTER[Territory], "ComisTot",  MAX( 0, Sales * Commission + BaseBonus )),
    --[ComisTot]
)


Wondering if this will do the trick.

 

Regards,

Tom

calerof's avatar
calerof
Impactful Individual
6 years ago

Hi TomMartens,

 

The commission is based on total sales by territory, not sales order.

 

I looked again more carefully at your second reply and tryed this change in my measure:

 

 

Commission 2= 
SUMX(
VALUES( OTER[Territory] ),
VAR Sales = [Sales Total]
VAR Margin = [Margin Total]
VAR Commission = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 0,
            Sales >= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
            Sales >= 250000 && Margin < 0.60, 0.015,
            Sales >= 250000 && Margin >= 0.6, 0.02,
            0
        )
VAR BaseBonus = 
        SWITCH( TRUE(),
            Sales >= 200000 && Sales < 250000, 1000,
            0
        )
VAR CalcComision = Sales * Commission + BaseBonus
RETURN
CalcComision
)

 

 

And it worked! ðŸ¤“

 

 

Simple is best!

 

Thank you very much for your help and the push to continue thinking on the best way to work around here with PBI.

 

Fernando