Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |