cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lbrown
Helper I
Helper I

Dax Measure for Commission Amount based on # Sales Between Two Numbers

I am attempting to create a dax measure that will calculate the total commission amount based on a filter of "eligible for payout" and the # Sales between two numbers. So if Validation 1 Column = "Eligible for Payout" and the # Sales Column is between 0-6 then I want to take the # Sales * the $ amount per sale.

 

Example: If Validation 1 Column = "Eligible for Payout" and the # Sales Column = 16 then I want to take 16 * $14 to get $224.

 

Another Example: If Validation 1 Column = "Eligible for Payout" and the # Sales Column = 5 then I want to take 5 * $7 to get $35. 

 

Want I want to see in a Power BI Table is this: 

Validation 1     # Sales    Commission Amount
Eligible for Payout 16$224
Eligible for Payout 15$210
Eligible for Payout 15$210
Eligible for Payout 9$81

 

What I currently have in a Power BI Table is this:

 Capture.JPG

 

The Commission Tier is this:

 

Capture2.JPG

2 REPLIES 2
Greg_Deckler
Super User IV
Super User IV

So, probably something like:

 

Measure =
VAR __sales = MAX([# Sales])
VAR __commission =
  SWITCH(TRUE(),
    __sales<=6,LOOKUPVALUE('Commissions'[$/Sale],'Commissions'[#Sales],"0-6"),
    __sales<=9,LOOKUPVALUE('Commissions'[$/Sale],'Commissions'[#Sales],"7-9"),
    __sales<=14,LOOKUPVALUE('Commissions'[$/Sale],'Commissions'[#Sales],"10-14"),
    __sales<=19,LOOKUPVALUE('Commissions'[$/Sale],'Commissions'[#Sales],"15-19"),
    LOOKUPVALUE('Commissions'[$/Sale],'Commissions'[#Sales],"20+")
)
VAR __eligible = MAX([Validation 1])
RETURN
IF([Validation 1]<>"Eligible for Payout",0,__sales * __commission)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks Greg,

 

I am receiving an error message on the If statement for [Validation 1] column. I forgot to mention the commission values are in a separate table titled "FL Commission Rates" and the query table holding the data is in a table titled "FL Agents Commission". There's no unique identifier to create a relationship between these two tables. Any suggestions on how to fix the error message?

 

Also, should the [# Sales] column be the column from the "FL Agents Commission" table which is actually a measure to get the count of comissions per agent or should I use the [# Sales] column from the "FL Commission Rates" which is the chronological number from 0-20 in a column? I'm a little confused on this part.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors