Calculating Weighted Sales

[Ilija89]

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

[v-dineshya]

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

[FBergamaschi]

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 solution

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

[Ilija89]

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.

[Ilija89]

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!

[Selva-Salimi]

Hi @Ilija89 ,

Please add an example of what you need as output? a table including customers, sales rep, and weights?!

[Ilija89]

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

[FBergamaschi]

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