Range Lookup

[Anonymous]

Hi Experts,

I have a data in 2 sheets one is fact table and one i am creating for targets for calculating commissions.

Below is the example of dataset, would really appreciate if anyone can provide me the solution.

In Excel we can do we lookup with approximate match, but how can we achieve this objective in PBI as I am calculating KPI achieved through measure for each location and want to see how much commission they have achieved so for?

[Anonymous]

Anyone?

[Anonymous]

@amitchandak @Fowmy 

[Anonymous]

Hi,

You have incorporated that in to the table, where I need that calculation in Table Visual. Because the Fact table is not comprises on few records. It has many of the records and I am Calculating achievements through measure.

https://drive.google.com/file/d/1Ejs6v0c16b3v09zN46h3iEBHW0QsIDzg/view?usp=sharing

Kindly use the link to pbix file

[Anonymous]

anyone can helpppp...??

[Fowmy]

@Anonymous 

Can you put the same data in a power bi file and show the expected results as well in the reply?

You save the file in OneDrive or Google Drive and share the link here

[Anonymous]

https://drive.google.com/file/d/1Ejs6v0c16b3v09zN46h3iEBHW0QsIDzg/view?usp=sharing
Please review

[V-lianl-msft]

Hi @Anonymous ,

1. Add custom columns for each table in power query

Text.Format("#[left]-#[right]",[left=Number.IntegerDivide([KPI]/10,1)*10-4,right=left+9])

2. Create a new column with DAX:

commission = LOOKUPVALUE('Table'[Commission ],'Table'[Location],'Table (2)'[Location],'Table'[distribution],'Table (2)'[Custom])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

As you can see every location has target (Attendees), Take a example of Motor City it has the range of Attendees, every incremental attendees the location achieve so the commission will increase.

Through this table I want to calculate the commission for each Location:

For example Motorcity has achieve 711 Attendess so the commission would be 750.