cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DylanLeong
New Member

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

 

TypeHolding CompanyCompany Spend Date
GasHLD - CompanyACompanyS 6,6752020-01-29
GasHLD - CompanyBCompanyT 9,2622020-01-29
GasHLD - CompanyACompanyV 2,4302020-01-29
GasHLD - CompanyBCompanyW 5,7852020-01-29
GasHLD - CompanyACompanyX 5,2392020-01-29
GasHLD - CompanyBCompanyZ 12,8352020-01-29
GasHLD - CompanyACompanyS 11,0152020-02-06
GasHLD - CompanyCCompanyT 7,2272020-02-06
GasHLD - CompanyDCompanyV 14,1042020-02-06

 

Holding CompanyRebate% Amount from  Amount To Date From Date To
HLD - CompanyA1.00% 20,000 40,0002020-01-012020-12-31
HLD - CompanyA3.00% 40,001 60,0002020-01-012020-12-31
HLD - CompanyA5.00% 60,001 9,999,9992020-01-012020-12-31
HLD - CompanyB2.00% 50,000 75,0002020-01-012020-12-31
HLD - CompanyB4.00% 75,001 100,0002020-01-012020-12-31
HLD - CompanyB6.00% 100,001 250,0002020-01-012020-12-31
HLD - CompanyC2.50% 150,000 175,0002020-01-012020-12-31

 

Expected output:

  Rebate applRebate bandCalc Rebate
 HLD - CompanyA  89,8785% 60,001 4,493.90
 HLD - CompanyB  99,3424% 75,001 3,973.68
 HLD - CompanyC  129,2960% 150,000 -00
 HLD - CompanyD  99,1092% 80,000 1,982.18
3 REPLIES 3
v-yuaj-msft
Community Support
Community Support

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

v-yuaj-msft
Community Support
Community Support

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)

v-yuaj-msft_0-1613541852109.png

 

Best Regards,

Yuna

 

amitchandak
Super User IV
Super User IV

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors