Incorrect total when using variables

[calerof]

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 a variable, which gest an incorrect total amount. Here's my code to get the commission:

Comision = 
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 CommissionAmount = Sales * Commission + BaseBonus
VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot",  MAX( 0, CommissionAmount ) )
RETURN
SUMX( ComisTotal, [ComisTot] )

This is the result I get with the measure above:

The resulting incorrect total amount is $188,573 instead of $18,569.

For simplification purposes I'm including an example with just a few sales to prove the error in the total amount using variables, as follows:

I have an excel file with 3 tables:

a) Fact table with sales transactions

b) Dim table with Customer data

c) Dim table with Sales Personnel data

In this example I'm just following an excercise to calculate the excess of $1,000 USD and get a total amount based on this measure using a variable.

My measure for the dummy data example, which works perfectly fine for row level but not for the total, is the following:

Excess = 
VAR ExcessAmount = SUM( Sales[Amount] ) - 1000
VAR ExcessTotal = SUMMARIZE( Sales, Slp[Slpcode], "ExcessTot", ExcessAmount )
RETURN
IF(
    HASONEFILTER( Slp[Slpcode] ),
    IF(
        SUM( Sales[Amount] ) < 1000,
        0,
        ExcessAmount
    ),
    SUMX(
        VALUES( Slp[Slpcode] ),
        ExcessAmount
    )
)

Here's my pbix file.

The result is as follows:

The total should be $6,300 and shows an incorrect total amount of $24,000.

My goal is to calculate commissions, not to fix the variable issue. So if there is a workaround that provides the best solution I'd appreciate a lot your support. 

Thanks,

Fernando

[TomMartens]

Hey @calerof ,

rewrite your measure like so, put the variables inside the iteration: 

SUMX(
	<table>
	, var v1 = ...
	, var v2 = ...
	, var v3 = v1 + v2
	return
	v3
)

The wrong Total is due to the absence of a filter context provided by the current row of each iteration.

Regards,

Tom

[calerof]

Hi @TomMartens,

I tryied the following measure per your reply:

Commission = 
SUMX(
    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
    SUMMARIZE( VALUES( OTER[Territory] ), OTER[Territory], "ComisTot",  MAX( 0, Sales * Commission + BaseBonus )),
    [ComisTot]
)

But the total is still incorrect:

I can't make up my mind how to put the table before the variables as I need them in the SUMMARIZE function.

Fernando

[TomMartens]

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 · Accepted solution]

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]

Hey @calerof ,

I guess it's necessary that create some sample data that still represents your data model, and upload the pbix and xlsx (if used for the sample data) to onedrive or dropbox and share the link. Please describe the expected result for the Total line, and how this result has to be calculated.

As you are using the column territory for the iteration, I'm wondering if there are more "sales orders" in one territory and if the comission has to be determined for each "sales order"?

Another question is about the sales bonus has to be determined for each "sales order" or for the sum of all sales in an individual territory.

Maybe these questions already will provide some ideas.

Regards,

Tom

[TomMartens]

Hey @calerof ,

from your sample pbix I rewrote the measure Excess like so:

Excess = 
VAR ExcessAmount = SUM( Sales[Amount] ) - 1000
VAR ExcessTotal = SUMMARIZE( Sales, Slp[Slpcode], "ExcessTot", ExcessAmount )
RETURN
/*
IF(
    HASONEFILTER( Slp[Slpcode] ),
    IF(
        SUM( Sales[Amount] ) < 1000,
        0,
        ExcessAmount
    ),
 */   
    SUMX(
        VALUES( Slp[Slpcode] ),
        var _salesamount = CALCULATE(SUM( Sales[Amount] ))
        return
        IF(_salesamount < 1000 , 0 , _salesamount - 1000)
    )
--)

Basically the measure just contains the iteration, no variables from outside the iteration are used and returns this:

 I guess this is what you expect.

I have to admit that I have no idea what you want to achieve with the SUMMARIZE from your orignial problem, can you please elaborate a little more on this.

Regards,
Tom

[amitchandak]

I changed formula like this.

Excess = 
sumx(SUMMARIZE( Sales, Slp[Slpcode], "ExcessTot", SUM( Sales[Amount] ) ),if([ExcessTot]<1000,0,[ExcessTot]-1000))

Push the calculation to summarize.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[calerof]

Hi @amitchandak,

Thanks for your response. I get that change, but my problem is with the commission calculation measure, how to change it to get it work.

Regards,

Fernando