Calculating Weighted Sales

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