Calculate and Lookup values from Static Table

[ashishshahs]

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

[amitchandak · Accepted solution]

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

[v-jayw-msft · Accepted solution]

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

[amitchandak]

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

[ashishshahs]

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

[amitchandak · Accepted solution]

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