cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Lookup On Commission basis criteria

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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper IV

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors