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.
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;
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 😞
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
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;
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])
)
)
No one that could help? 😞
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |