Forum Discussion
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 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
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
8 Replies
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 LinkedinHi 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
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
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
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
