Forum Discussion
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 commission for the Sales Person based on 1) weekly sales commission (irrespective of customer), 2) Quarterly Sales Commission. The commission is stored in a separate table with tiers e.g;
0-150 = 1.5%
151- 450 = 2% and so on.
I tried the LOOKUPVALUE function but it did not lookup in the static standalone commission table. Not sure why it does not bring up the values. Any guidance is appreciated.
-Ashish
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])
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*commissResult would be shown as below.
Best Regards,
jay
4 Replies
ashishshahs ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.Hello Amit,
Thanks for your response. The Sales Table loks something like the below ...
Customer Week Margin Sales Customer 1 1 32.0155 John Perry Customer 2 1 2.716 Chuck Jones Customer 3 2 7.15 Chuck Jones Customer 2 2 2.716 Chuck Jones Customer 2 2 2.716 Chuck Jones Customer 2 2 2.716 Chuck Jones Customer 3 3 7.15 Chuck Jones Customer 1 3 32.0155 John Perry Customer 1 3 32.0155 John Perry Customer 1 3 32.0155 John Perry The commission table looks something like ...
Net Margin in $ Lower Upper Commission 0-150 0 150 0.015 151-450 150 450 0.02 451-650 450 650 0.03 651-850 650 850 0.04 851-1000 850 1000 0.045 1001-1200 1000 1200 0.05 1200+ 1200 5000 0.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
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])
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*commissResult would be shown as below.
Best Regards,
jay
