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

Lookupvalue Multiple values - Business Case

Hi Community, 

 

I have the following issue, I need to BI to lookup multiple values, in different tables, and return a value meeting those criterias. 

 

I have the following parameters; 

 

  • Services (a service catalouge)
  • Volume table - this table is not dependant on any of the criterias we'll get back to 
  • PriceVersions - I have 3 different sets of price tenders from the same supplier
  • Scenario Table - Different scenarios given different suppliers will be awarded different Services

 

But, in plain english, I like the formula to reflect something like this:

 

"For Scenario1, find the price per service iten (SIN)  by looking up "PriceVersions" in "ScenarioTable" and then multply by volume."

 

EXAMPLE:

 

For Scenario 1, The prices that should be looked up, and mulitplied by a volume (any volume table really) should therefore be:

 

Legacy Systems = Google1

 

Google1 = PriceVersion

 

So, it should look in the "ScenarioTable" after;

Services

Scenario

PriceVersion

 

It should lookup the scenario in the ScenarioTable, then;

In the ServicesTable, it should that ScenarioID is Legacy Systems, then;

identify that the combination of Legacy Systems and Scenario1 corresponds to teh PriceVersion "Google1" and the actual values it should return would be;

 

SIN01 -699

SIN02 -6077

SIN03 - and so on

SIN04 - as above

SIN05 - as above

 

 

Screenshot_25.png

 

 

 

 

Screenshot_22.png

 

Screenshot_24.png

 

Screenshot_23.png 

 

NOTE: Yes it should also find a specific price per year, I just havent got the time to fix my Date table yet, and its getting really late here.

 

Been struggling for quite some time and cant find a specific solution in the forum for this one 😞 

 

EXAMPLE FILES CAN BE FOUND HERE

3 REPLIES 3
tonijj Member
Member

Re: Lookupvalue Multiple values - Business Case

No one that could help? 😞 

Microsoft v-yulgu-msft
Microsoft

Re: Lookupvalue Multiple values - Business Case

Hi @tonijj,

 

Unfortunately, I have spent a long time to read above post, but with so many tables and relationships, I was not able to understand your actual requirement. Would you please remove some unnecessay tables and columns, and divide the whole requirement into some specific steps so that I can try to work out some of them.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tonijj Member
Member

Re: Lookupvalue Multiple values - Business Case

@v-yulgu-msft

 

Hi Yuliana, 

 

Ok I will try to divide it better. 

 

So, I want the formula to do the following; Find the correct PRICE with the given parameters;

 

  • (1) Scenario 
  • (3) PriceVersion - which pricebook should be used, based on scenario above (and supplier)
  • Supplier - In the scenario table either Google OR Apple is rewarded a contract
  • (2) ScenarioService - which services are in scope.
  • (4) SIN the unique key combining Service
  • (5) Price - The end result that I want BI to find. 

 

 

I added an example in the excel file, but will post it below as well. 

 

 

The formula Im thinking of is something like this, but...I cant get it to work. 

 

Price Per Month =

CALCULATE(
    SUMX( VolumeSC1;
      VolumeSC1[Volume_A] * LOOKUPVALUE(PricetableC[Price]; PricescenarioC[PriceVersion]; ScenarioVolume[Scenario]; ; VolumeSC1[SIN]; PricetableC[SIN]; VolumeSC1[Supplier]; PricescenarioC[Supplier])
    )
)

Scenarioswalkthrough.png

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors