Forum Discussion

tonijj's avatar
tonijj
Helper IV
8 years ago

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

 

 

 

 

 

 

 

 

 

 

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

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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

    • tonijj's avatar
      tonijj
      Helper IV
      8 years ago

      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])
          )
      )