topic Re: Calculating Weighted Sales in DAX Commands and Tips

Calculating Weighted Sales

Ilija89 — Mon, 08 Sep 2025 09:34:23 GMT

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

Re: Calculating Weighted Sales

FBergamaschi — Mon, 08 Sep 2025 13:02:54 GMT

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

Re: Calculating Weighted Sales

Ilija89 — Mon, 08 Sep 2025 13:32:13 GMT

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!

Re: Calculating Weighted Sales

FBergamaschi — Mon, 08 Sep 2025 17:39:26 GMT

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

Re: Calculating Weighted Sales

Selva-Salimi — Tue, 09 Sep 2025 11:21:04 GMT

Hi Ilija89 ,

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

Re: Calculating Weighted Sales

v-dineshya — Tue, 09 Sep 2025 11:38:16 GMT

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

Re: Calculating Weighted Sales

Ilija89 — Tue, 09 Sep 2025 11:51:43 GMT

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

Re: Calculating Weighted Sales

Ilija89 — Tue, 09 Sep 2025 12:14:55 GMT

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.

Re: Calculating Weighted Sales

v-dineshya — Fri, 12 Sep 2025 13:58:15 GMT

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

Re: Calculating Weighted Sales

v-dineshya — Mon, 15 Sep 2025 17:02:42 GMT

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

Re: Calculating Weighted Sales

Ilija89 — Tue, 16 Sep 2025 07:43:17 GMT

Hi v-dineshya ,

Sorry for late reply. Thank you for solution, but I have one remark. In pbix file all sales reps have the same indirect sales but it shouldnt be. Formula is summing indirect sales and it is the same for all sales reps. G.L. should have 43,342 and A.K. 7,878 indirect sales while other sales reps should have 0.

Re: Calculating Weighted Sales

Selva-Salimi — Tue, 16 Sep 2025 14:08:04 GMT

Hi Ilija89

Thanks to v-dineshya , I think you need to update the measure as follows:

Weighted Sales =

SUMX (

Sales,

SWITCH (

TRUE(),

Sales[Sales Type] = "Indirect",

CALCULATE(sum(Sales[Sales amount]),FILTER(Sales,Sales[Sales Rep]=SELECTEDVALUE(Weights[Sales Representative]))),

Sales[Sales Type] = "Direct",

Sales[Sales amount] *

CALCULATE (

MAX ( Weights[Weight] ),

TREATAS ( { Sales[Category-Customer Code] }, Weights[Category-Customer Code] ),

TREATAS ( VALUES ( Weights[Sales Representative] ), Weights[Sales Representative] )

)

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Calculating Weighted Sales

v-dineshya — Tue, 16 Sep 2025 10:49:13 GMT

Hi Ilija89 ,

Please refer below updated DAX measure.

Weighted Sales =

SUMX (

Sales,

SWITCH (

TRUE(),

Sales[Sales Type] = "Indirect" &&

SELECTEDVALUE ( Weights[Sales Representative] ) = Sales[Sales Rep],

Sales[Sales amount],

Sales[Sales Type] = "Direct",

Sales[Sales amount] *

CALCULATE (

MAX ( Weights[Weight] ),

TREATAS ( { Sales[Category-Customer Code] }, Weights[Category-Customer Code] ),

TREATAS ( VALUES ( Weights[Sales Representative] ), Weights[Sales Representative] )

)

Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Re: Calculating Weighted Sales

Ilija89 — Wed, 17 Sep 2025 07:53:07 GMT

Hi v-dineshya. thanks for replying, solution is partly correct. New formula assigns indirect value for sales reps but for all customers. For this particular case, indirect sales is available only for AB customer, category 1, for sales rep G.I. 43.342 and KG customer for sales rep A.K. 7.878 for category 2. For al lother customers we should have values, but this formula asign these values for all customers. Coul you check? thank you a lot for help!

Re: Calculating Weighted Sales

Ilija89 — Wed, 17 Sep 2025 07:55:21 GMT

Thanks Selva-Salimi! I tried it, but it asigns indirect values for all customers for slaes reps that we have indirect sales. Do you know what we should change in calculation?

Re: Calculating Weighted Sales

Selva-Salimi — Wed, 17 Sep 2025 13:01:18 GMT

Ilija89 ,

Are you sure that you use "SELECTEDVALUE"?? would you please share your measure? and also would you please tell me about the relations between the tables? are they "single" direction?

Re: Calculating Weighted Sales

Ilija89 — Wed, 17 Sep 2025 13:08:26 GMT

hI Selva-Salimi , latest measure attached:

Weighted Sales =

SUMX (

Sales,

SWITCH (

TRUE(),

Sales[Sales Type] = "Indirect" &&

SELECTEDVALUE ( Weights[Sales Representative] ) = Sales[Sales Rep],

Sales[Sales amount],

Sales[Sales Type] = "Direct",

Sales[Sales amount] *

CALCULATE (

MAX ( Weights[Weight] ),

TREATAS ( { Sales[Category-Customer Code] }, Weights[Category-Customer Code] ),

TREATAS ( VALUES ( Weights[Sales Representative] ), Weights[Sales Representative] )

)

Relations are "single" direction.

Re: Calculating Weighted Sales

Selva-Salimi — Wed, 17 Sep 2025 14:25:52 GMT

Ilija89 ,

would you please update the "indirect" part, the same as what I have mentioned in previous post? I mean...

Sales[Sales Type] = "Indirect",

CALCULATE(sum(Sales[Sales amount]),FILTER(Sales,Sales[Sales Rep]=SELECTEDVALUE(Weights[Sales Representative]))),

Re: Calculating Weighted Sales

Anonymous — Fri, 19 Sep 2025 08:09:21 GMT

Hi Ilija89,

I am also part of the CST team. Could you please share the expected output from the sample data you provided? This will help us investigate further and work on the measure effectively.

Apologies that the issue still persists, and thank you for your patience.

Regards,

B Manikanteswara Reddy

Re: Calculating Weighted Sales

v-dineshya — Mon, 22 Sep 2025 15:37:29 GMT

Hi Ilija89 ,

Could you please share the expected output from the sample data you provided? This will help us investigate further and work on the measure effectively. Apologies that the issue still persists, and thank you for your patience.

Regards,

Dinesh