Forum Discussion

datadonuts's avatar
datadonuts
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Commission calculation in a measure

I guess for most of you that's a fairly simple task, unfortunately not for me.

I am trying to calculate the commission on sales depending on a country table:

'Sales Territory'[Country] ) = "United States" gets 15% commission, all other countries get 10%.

 

With a calculated column that's straight forward, but I want a measure.

 

Calculated column commission =
IF (
RELATED ( 'Sales Territory'[Country] ) = "United States",
[Revenue] * 0.15,
[Revenue] * 0.1
)
 
Expected Result
 
I tried my way through CALCULATE and IF or SWITCH, but no way to get any close to a solution. The closest I could get is:
 
Sales Commissions =
[Revenue]
* IF (
HASONEVALUE ( 'Sales Territory'[Country] ),
IF ( VALUES ( 'Sales Territory'[Country] ) = "United States", 0.15, 0.1 )
)
 
But it doesn't show a total bc of the HASONEVALUE function.
 
 

 

 
Please help.
Thanks a lot!
 
 
 
 
 
 
CALCULATE
dax measure
if statement
  • wdx223_Daniel's avatar
    wdx223_Daniel
    6 years ago

    datadonuts please try this code

    SUMX(VALUES('Sales Territory'[Country]),IF (
 'Sales Territory'[Country]  = "United States",
[Revenue] * 0.15,
[Revenue] * 0.1
))

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    6 years ago

    datadonuts please try this code

    SUMX(VALUES('Sales Territory'[Country]),IF (
 'Sales Territory'[Country]  = "United States",
[Revenue] * 0.15,
[Revenue] * 0.1
))