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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

     

  • 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.