Forum Discussion
Translate Commission formula (calculated column)
I want to translate a formula to create a calculated column (commission) in Power BI. I have three attributes in a table "SalesRepresetativeID", "Order Sum", and "OrderNumber". The sum of (Order sum) column as per Employee (SalesRepresentativeID) column divided the sum with 17.
For instance; if the sum of "SalesRepresentativeID" (87487) is 210€, then the commission will be 35.7€. This commission will save in another column on the particular "SalesRepresentativeID" The formula will be -> 210*17/100.
Please see the shared image of the demo dataset.
I will be grateful if you help me with that. Thanks in advance
Anonymous try this
New Column = VAR __denominator = 17 VAR __sales = CACLULATE ( SUM ( Table[Order Amt] ), ALLEXCEPT ( Tabe, Table[SalesRep] ) ) RETURN DIVIDE ( __sales, __denominator ) * 100Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
5 Replies
- parry2k
Super User
Anonymous try this
New Column = VAR __denominator = 17 VAR __sales = CACLULATE ( SUM ( Table[Order Amt] ), ALLEXCEPT ( Tabe, Table[SalesRep] ) ) RETURN DIVIDE ( __sales, __denominator ) * 100Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
I tried multiple things and your solution works well for me. I changed a bit and it looks like it is working now.
Here is the formula: -
Example_Commission =
VAR __denominator = 100
VAR __sales = CALCULATE(SUM(Table[OrderSum]),ALLEXCEPT(Table,Table[SalesRepresentativeID]))
Return
DIVIDE(__sales,__denominator)*17Maybe you can have a look at this question and if you have the idea?