Forum Discussion

ashishshahs's avatar
ashishshahs
New Member
5 years ago
Solved

Calculate and Lookup values from Static Table

Greetings All,   I have a data model, that stores NetMargin Amount, Sales Person, Customer per week. So, in short, have multiple values for the year (52 weeks). I have a need to calculate the commi...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    ashishshahs , Try a measure like

     

    sumx(summzarize(Table, Table[Customer], Table[Week], "_1", calculate(sumx(filter(Table, [margin Sum] >=min(commission[Lower]) && [margin Sum] <=max(commission[Upper])), [margin Sum]*max(commission[Commission])))),[_1])

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ashishshahs ,

     

    Check the formula below.

    Column = 
var summargin = CALCULATE(SUM('Sales'[Margin]),ALLEXCEPT('Sales','Sales'[Week],'Sales'[Sales]))
var commiss = CALCULATE(MAX('commission'[Commission]),FILTER('commission','Sales'[Margin]>'commission'[Lower]&&'Sales'[Margin]<='commission'[Upper]))
return
summargin*commiss

    Result would be shown as below.

     

     

    Best Regards,

    jay

ashishshahs's avatar
ashishshahs
New Member
5 years ago

Hello Amit,

 

Thanks for your response. The Sales Table loks something like the below ...

CustomerWeekMarginSales
Customer 1132.0155John Perry
Customer 212.716Chuck Jones
Customer 327.15Chuck Jones
Customer 222.716Chuck Jones
Customer 222.716Chuck Jones
Customer 222.716Chuck Jones
Customer 337.15Chuck Jones
Customer 1332.0155John Perry
Customer 1332.0155John Perry
Customer 1332.0155John Perry

 

The commission table looks something like ... 

 
Net Margin in $LowerUpperCommission
0-15001500.015
151-4501504500.02
451-6504506500.03
651-8506508500.04
851-100085010000.045
1001-1200100012000.05
1200+120050000.06
 

 

 

Example: John Perry, for Week 3, the total of his margin is 96.05 and his commission should be 96.05*0.015.

 

-Ashish

 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ashishshahs , Try a measure like

     

    sumx(summzarize(Table, Table[Customer], Table[Week], "_1", calculate(sumx(filter(Table, [margin Sum] >=min(commission[Lower]) && [margin Sum] <=max(commission[Upper])), [margin Sum]*max(commission[Commission])))),[_1])