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

[v-dineshya]

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

[v-dineshya]

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

[Ilija89]

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. 

[v-dineshya]

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] )

            ),

        0

    )

)

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

[Ilija89]

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!

[v-dineshya]

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

[Ilija89]

Hi @v-dineshya ,

thanks for assistance, below is expected output. 

	Direct	Indirect	Total
A.K	1,587,374	7,878	1,595,252
AB	105,856	-	105,856
1	105,856		105,856
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
KG	-	7,878	7,878
2		7,878	7,878
B.J	1,481,519	-	1,481,519
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
G.L	1,843,742	43,342	1,887,084
AB	317,567	43,342	360,909
1	317,567	43,342	360,909
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
AF	44,656	-	44,656
1	44,656		44,656

[v-dineshya]

Hi @Ilija89 ,

Thanks for the update, Could you please elaborate the logic behind the expected output or Please explain your query in detail. I have tried all the options, but i am not getting the expected output.

Regards,

Dinesh

[Ilija89]

Hi @v-dineshya ,

Logic is explained below:

	Direct	Indirect	Total	Logic
A.K	1,587,374	7,878	1,595,252
AB	105,856	-	105,856
1	105,856		105,856	Direct sales from customer AB, we are multypling direct sales of AB with weight - 25% out of 423.423
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519	Direct sales from customer AC, we are multypling direct sales of A.C with weight - 33% out of 4.444.556
KG	-	7,878	7,878
2		7,878	7,878	This is indirec tsales from A.K. so it is 7.878 it is wo using weights
B.J	1,481,519	-	1,481,519
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519	Direct sales from customer AC, we are multypling direct sales of A.C with weight - 33% out of 4.444.556
G.L	1,843,742	43,342	1,887,084
AB	317,567	43,342	360,909
1	317,567	43,342	360,909	Direct sales from customer AB, we are multypling direct sales of AB with weight - 75% out of 423.423 while indirect sales corresponds to indirect sales of G.L which is 43.342
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519	Direct sales from customer AC, we are multypling direct sales of A.C with weight - 33% out of 4.444.556
AF	44,656	-	44,656
1	44,656		44,656	Direct sales from customer AF, we are multypling direct sales of AF with weight - 100%(1) out of 44.656

[v-dineshya · Accepted solution]

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] )

RETURN

SUMX (

    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

        )

    RETURN

        TotalDirectForCust * COALESCE ( WeightVal, 0 )

)

Indirect Sales Allocated =

VAR CurrentRep = SELECTEDVALUE ( Weights[Sales Representative] )

RETURN

CALCULATE (

    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

[v-dineshya]

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

[Ilija89]

Weight table

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	1	Jan-25	1ac	4,444,556	Direct	1_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

[v-bmanikante]

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

[Ilija89]

Hi @v-bmanikante  ,

thanks for assistance, below is expected output. 

	Direct	Indirect	Total
A.K	1,587,374	7,878	1,595,252
AB	105,856	-	105,856
1	105,856		105,856
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
KG	-	7,878	7,878
2		7,878	7,878
B.J	1,481,519	-	1,481,519
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
G.L	1,843,742	43,342	1,887,084
AB	317,567	43,342	360,909
1	317,567	43,342	360,909
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
AF	44,656	-	44,656
1	44,656		44,656

[Ilija89]

Weight table

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	1	Jan-25	1ac	4,444,556	Direct	1_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

[Selva-Salimi]

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.

[Ilija89]

Hi @Selva-Salimi ,

thanks for assistance, below is expected output. Even if I changed formula based on your input it gives me different output than expacted. 

	Direct	Indirect	Total
A.K	1,587,374	7,878	1,595,252
AB	105,856	-	105,856
1	105,856		105,856
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
KG	-	7,878	7,878
2		7,878	7,878
B.J	1,481,519	-	1,481,519
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
G.L	1,843,742	43,342	1,887,084
AB	317,567	43,342	360,909
1	317,567	43,342	360,909
AC	1,481,519	-	1,481,519
1	1,481,519		1,481,519
AF	44,656	-	44,656
1	44,656		44,656

[Ilija89]

Weight table

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	1	Jan-25	1ac	4,444,556	Direct	1_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

[Ilija89]

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?