Forum Discussion
Lookup tier with multiple results expected
I need a dynamic lookup on a table consisting of tiers
Table A:
Type
Holding Company
Company
Spend
Date
Table B
Type
Holding Company
Company
Rebate
Amount from
Amount to
Date from
Date to
The user should be able to filter by Type, Holding Company ,Company and see the total spend for a particular date range selected from Table A in their report
The total amount (for the date range selected) should look up table B and find the correct rebate amount in the tier for selection.
The user should be able to select a Company within the Holding company and that spend total should correspond to the tier for that Company in Table B to obtain the rebate - The total spend will differ on a Holding Company versus a Company. Mutiple values is expected as the user should be able to select multiple Holding Companies in their selection
| Type | Holding Company | Company | Spend | Date |
| Gas | HLD - CompanyA | CompanyS | 6,675 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyT | 9,262 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyV | 2,430 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyW | 5,785 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyX | 5,239 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyZ | 12,835 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyS | 11,015 | 2020-02-06 |
| Gas | HLD - CompanyC | CompanyT | 7,227 | 2020-02-06 |
| Gas | HLD - CompanyD | CompanyV | 14,104 | 2020-02-06 |
| Holding Company | Rebate% | Amount from | Amount To | Date From | Date To |
| HLD - CompanyA | 1.00% | 20,000 | 40,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyA | 3.00% | 40,001 | 60,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyA | 5.00% | 60,001 | 9,999,999 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 2.00% | 50,000 | 75,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 4.00% | 75,001 | 100,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 6.00% | 100,001 | 250,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyC | 2.50% | 150,000 | 175,000 | 2020-01-01 | 2020-12-31 |
Expected output:
| Rebate appl | Rebate band | Calc Rebate | ||
| HLD - CompanyA | 89,878 | 5% | 60,001 | 4,493.90 |
| HLD - CompanyB | 99,342 | 4% | 75,001 | 3,973.68 |
| HLD - CompanyC | 129,296 | 0% | 150,000 | -00 |
| HLD - CompanyD | 99,109 | 2% | 80,000 | 1,982.18 |
3 Replies
- amitchandak
Super User
DylanLeong , You need ti few common dimensions like date, Type, Holding Company , Company (in one ore moew dimensions ) and join both tables with them and analyze them together
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
Hi DylanLeong ,
Sorry for that I'm still confused to get your desired output. Could you please explain with an example? (eg. the first row of your desired table)
Best Regards,
Yuna
Hi DylanLeong ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Yuna
