Forum Discussion
Calculating Weighted Sales
I need help in calculating weighted sales.
Context:
There are two tables; Sales transaction and Sales Weights tables (examples attached with dummy data).
Sales is divided into direct and indirect sales. If it is indirect sales is alocated to sales rep. who is covering that specific customer. On the other side if sales is direct, then sales amount should be splitted to sales reps by weights given in Sales Weights table.
Relationship between tables:
In order to create relationship between two tables, bridge table is created with unique code created by merging category and customer code.
How to create formula that will calculate weighted sales for direct sales for each sales rep per customer per category? Some switch formula
Thanks
Bridge table
Sales table
Weights table
Hi Ilija89 ,
Thank you for the response, I have tried the solution based on your logic, but it is not giving exact result. you need to change your data and data model.
Please refer below two solutions.
1. Direct Sales Allocated :=
SUMX(
FILTER( Sales, Sales[Sales Type] = "Direct" ),
VAR CatCust = Sales[Category-Customer Code]
VAR Amount = Sales[Sales amount]
VAR CurrentRep = SELECTEDVALUE( Weights[Sales Representative] )
VAR WeightVal =
CALCULATE(
MAX( Weights[Weight] ),
FILTER(
Weights,
Weights[Category-Customer Code] = CatCust
&& Weights[Sales Representative] = CurrentRep
)
)
RETURN Amount * COALESCE( WeightVal, 0 )
)Indirect Sales Allocated :=
SUMX(
FILTER( Sales, Sales[Sales Type] = "Indirect" ),
IF( Sales[Sales Rep] = SELECTEDVALUE( Weights[Sales Representative] ),
Sales[Sales amount],
0
)
)please refer output snap and PBIX file.
2.
Direct Sales Allocated =VAR CurrentRep = SELECTEDVALUE ( Weights[Sales Representative] )RETURNSUMX (VALUES ( Weights[Category-Customer Code] ),VAR CatCust = SELECTEDVALUE ( Weights[Category-Customer Code] )VAR CustCode = SELECTEDVALUE ( Weights[Customer code] )VAR Cat = SELECTEDVALUE ( Weights[Category] )VAR TotalDirectForCust =CALCULATE (SUM ( Sales[Sales amount] ),Sales[Sales Type] = "Direct",Sales[Customer code] = CustCode)VAR WeightVal =CALCULATE (MAX ( Weights[Weight] ),Weights[Category-Customer Code] = CatCust,Weights[Sales Representative] = CurrentRep)RETURNTotalDirectForCust * COALESCE ( WeightVal, 0 ))Indirect Sales Allocated =VAR CurrentRep = SELECTEDVALUE ( Weights[Sales Representative] )RETURNCALCULATE (SUM ( Sales[Sales amount] ),Sales[Sales Type] = "Indirect",Sales[Sales Rep] = CurrentRep)I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
29 Replies
Hi Ilija89
I am almost there but it seems to me that the tables are not complete (?)
For example, in Sales there is a row for Cust KG, category 2 and Salesman AK which is not refelected in the weights table, am I wrong? If you can cover all the Sales cases in the weights table, that will help me debug and send you the solutionThanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi this is the case that I am facing with. In tis particualr situation if for specific customer sales rep is asigned then sales is equal to that sales rep, in other case when sales is direct then we should calculate that row by multypling that sales with weights given for that customer.
Hi Ilija89,
it seems nothing difficult but to help you in the best possible way, please can you attach the tables with dummy data in a usable format? Not an image but text
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Weights
Customer Category Customer code Sales Representative Weight Category-Customer Code AB 1 1ab A.K 25% 1_1ab AB 2 1ab B.J 100% 2_1ab AB 1 1ab G.L 75% 1_1ab AC 1 1ac A.K 33% 1_1ac AC 1 1ac B.J 33% 1_1ac AC 1 1ac G.L 33% 1_1ac AF 2 1af A.K 50% 2_1af AF 2 1af B.J 50% 2_1af AF 1 1af G.L 100% 1_1af KG 1 1kg A.K 50% 1_1kg KG 2 1kg B.J 100% 2_1kg KG 1 1kg G.L 50% 1_1kg Sales table
Customer Sales Type Category Date Customer code Sales amount Sales Rep Category-Customer Code AB Direct 1 Jan-25 1ab 423,423 Direct 1_1ab AB Indirect 1 Feb-25 1ab 43,342 G.L 1_1ab AC Direct 2 Jan-25 1ac 4,444,556 Direct 2_1ac AF Direct 1 Feb-25 1af 44,656 Direct 1_1af KG Indirect 2 Feb-25 1kg 7,878 A.K 2_1kg Bridge table
Customer Category Customer code Category-Customer Code AB 1 1ab 1_1ab AB 2 1ab 2_1ab AC 1 1ac 1_1ac AC 2 1ac 2_1ac AF 1 1af 1_1af AF 2 1af 2_1af KG 1 1kg 1_1kg KG 2 1kg 2_1kg Hi FBergamaschi above are dummy data examples.
Thanks!
Hi Ilija89 ,
Please add an example of what you need as output? a table including customers, sales rep, and weights?!
Hi Selva-Salimi I need output as total sales of particular sales rep (sum of direct and indirect sales).per category per customer.
Thanks, Ilija
Hi Ilija89 ,
Thank you for reaching out to the Microsoft Community Forum.
You are expecting formula that will calculate weighted sales for direct sales for each sales rep per customer per category.
Please refer below output snap and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi Ilija89 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @Ilija89 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
