Lookup On Commission basis criteria

[bilalkhokar73]

I have one column that is achived %
I have one criteria table  where crietrai is there that
0-10 % =200Dollar commission
10-50%=300 dollar commission
50-80% =400 Dollar Commsiion

I want that if achived % is 60 in achived % column then it shwould look up and give me 400 dollar in new column 
If acihved % is 30 % then it shoudl give me 300 Dollar

If achived % is minus then need 0 liek that 

@amitchandak 

[DataVitalizer · Accepted solution]

Hi @bilalkhokar73 
I have for you two solutions:

1. I suggest that you create a new caclculaed column in the table with values using the following code

SWITCH(TRUE(),
AND('Table'[Colonne 1]>0,'Table'[Colonne 1]<=0.1),"A",
AND('Table'[Colonne 1]>0.1,'Table'[Colonne 1]<=0.5),"B",
AND('Table'[Colonne 1]>0.5,'Table'[Colonne 1]<=0.8),"C",
"D")

For the returned values (A, B, C and D) you can replace them by the commision 

2. Otherwise you can use the fomula in language M context through Power Query then merge your fact table with the commision

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated

[bilalkhokar73]

@Fowmy