Forum Discussion

Ilija89's avatar
Ilija89
Icon for Helper I rankHelper I
1 year ago
Solved

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 weights"
"DAX " "DAX help" 'Need help'
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    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

     

Ilija89's avatar
Ilija89
Icon for Helper I rankHelper I
10 months ago

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,856Direct 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,519Direct 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,878This 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,519Direct 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,909Direct 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,519Direct 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,656Direct sales from customer AF, we are multypling direct sales of AF with weight - 100%(1) out of 44.656
v-dineshya's avatar
v-dineshya
Icon for Community Support rankCommunity Support
10 months ago

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