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!

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

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? 

[Selva-Salimi]

@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?

[Ilija89]

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

            ),

        0

    )

)

Relations are "single" direction.

[Selva-Salimi]

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

[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