Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Range Lookup

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 prov...
Need Help
Fowmy's avatar
Fowmy
Super User
4 years ago

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

V-lianl-msft's avatar
V-lianl-msft
Community Support
4 years ago

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.