3 Table Lookup matching 3 column criteria

[Dave-ExpSC]

Please help with this formula. I want to populate the Energytbl(EFlook column) with matching data from the EFtbl(kgco2e). The two tables are connected by a third table (FacilityIDtbl) that has the 1 to many relationship to the Energytbl and the EFtbl. The lookup formula needs to find the value in the EFtbl(kgco2e) column that matches the Energytbl on 3 columns (FacilityID, eSource, and Year). 

Can an astute participant of this forum please help me with the formula? It would be greatly appreciated.

ENERGY tbl

	FacilityID	eSource	Category	Unit	Applicable	Year	Month	Quantity	EFlook
1	CA-IR-BA	Natural Gas	Energy-Sc2	kWh	Yes	2018	Jan	10,731,603
2	SY-UK-HI	Natural Gas	Energy-Sc2	kWh	Yes	2018	Feb	11,016,225
3	SY-BR-SA	Natural Gas	Energy-Sc2	kWh	No	2018	Mar	12,420,095
4	SY-TH-SA	Natural Gas	Energy-Sc2	kWh	No	2018	Apr	24,469,361
5	CA-IR-BA	Natural Gas	Energy-Sc2	kWh	Yes	2018	May	27,377,075
6	CA-IR-BA	Natural Gas	Energy-Sc2	kWh	Yes	2018	Jun	22,710,067

EFtbl

FacilityID	Year	eSource	kgco2e
CA-IR-BA	2018	Natural Gas	0.20437
CA-IR-BA	2018	Diesel	0.28307
CA-IR-BA	2018	Electricity	2.53627
CA-IR-BA	2018	LPG	10

Facilitytbl

Count	FacilityID	Company	Country	City
1	CA-IR-BA	XYZcompany	IR	Ballineen
2	SY-UK-HI	XYZcompany	UK	High Wycombe
3	SY-BR-SA	XYZcompany	BR	Sao Paulo
4	SY-TH-SA	ABC company	TH	Samutprakarn
5	SY-IT-TR	ABC company	IT	Trieste
6	SY-US-RO	ABC company	US	Rochester
7	SY-US-HA	ABC company	US	Hamilton
8	SY-US-WA	ABC company	US	Wauconda

[AlB]

Hi @Dave-ExpSC 

Try this

EFlook =
LOOKUPVALUE (
    EFtbl[kgco2e],
    EFtbl[FacilityID], Energytbl[FacilityID],
    Energytbl[Source], EFtbl[FacilityID],
    Energytbl[Year], EFtbl[FacilityID]
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[AlB · Accepted solution]

Sorry I must have messed it up when formatting

EFlook =
LOOKUPVALUE (
    EFtbl[kgco2e],
    EFtbl[FacilityID], Energytbl[FacilityID],
    EFtbl[Source], Energytbl[Source],
    EFtbl[Year], Energytbl[Year]
)

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Dave-ExpSC]

AIB - you were very helpful recently. I have gotten stuck on another formula. I hope you can help me out again.

I'm learning the hard way.

TARGETtbl
Count	FacilityID	Year	Target
1	CA-IR-BA	2018	10.00%
2	SY-UK-HI	2018	5.00%
3	SY-BR-SA	2018	12.00%
4	SY-TH-SA	2018	6.00%
5	SY-IT-TR	2018	4.00%
6	SY-US-RO	2018	8.00%
7	SY-US-HA	2018	9.00%
8	SY-US-WA	2018	7.00%
9	CA-IR-BA	2019	10.00%
10	SY-UK-HI	2019	10.00%
11	SY-BR-SA	2019	10.00%
12	SY-TH-SA	2019	10.00%
13	SY-IT-TR	2019	10.00%
14	SY-US-RO	2019	10.00%
15	SY-US-HA	2019	10.00%
16	SY-US-WA	2019	10.00%

ENERGYtbl
FacilityID	eSource	Year	Month	Quantity	mtCO2e	Gigajoules	mtCO2ePERGJ
CA-IR-BA	Natural Gas	2018	Jan	10,731,603	1,974	38,634	0.0511
SY-UK-HI	Natural Gas	2018	Feb	11,016,225	2,027	39,658	0.0511
SY-BR-SA	Natural Gas	2018	Mar	12,420,095	2,285	44,712	0.0511

I use this formula to calculate the mtCO2ePERGJ across multiple facilities

mt CO2e per GJ:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules]))

ENERGYtbl and TARGETtbl are both connected to the Facilitytbl with unique Facility IDs

This is a piece of the output pivot table

Sum of mtCO2e	47,360	335	285
Sum of Gigajoules	967,402	3,816	4,981
Intensity mt CO2e per GJ	0.0490	0.0878	0.0572
% Change mtCO2e previous year	-2.21%	619.02%	16.57%
% Change GJs previous year	-3.11%	413.22%	124.41%
Target Intensity	0.04663	0.08357	0.05449
mt CO2e per GJ Status	1	1	0

Target Intensity is a 5% reduction: 

Problem: The reduction is hardcoded because I can't get RELATED or LOOKUPVALUE to bring the matching value into my equation

Target Intensity:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])*1.05)

The 1st part of this equation works.

I just need to replace the "1.05" with a formula to find the multiplier "Target" in the TARGETtbl.

It has to match FacilityID and Year in between ENERGYtbl and TARGETtbl

I tried RELATED and LOOKUPVALUE

Related tells me it can't find the relationship --- but it's there

LOOKUPVALUE - I can't get past the search value argument (it's not a specific value)

I have spent days trying solutions - learned a lot - but can't finish it.

Help would be greatly appreciated.

[Dave-ExpSC]

oops I posted twice

[AlB]

@Dave-ExpSC 

I'm getting lost. 

1. Is Target Intensity a measure?Where and how exactly do you intend to use it? in a matrix visual? with what columns on the visual?

2. Can you provide an example where you show exactly where "the multiplier "Target" in the TARGETtbl" comes from?  Based on your sample and with numbers, so that I can follow it.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Dave-ExpSC]

Hi AIB, thanks for your help.

First of all, I should say that this project is in PowerPivot. When I’m done, I’ll replicate it in PowerBI to get more comfortable in that platform.

Target Intensity is a Measure.

Target Intensity:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])*1.05)

It is used in the Pivot Table shown here:

	FacilityIDs	CA-IR-BA	SY-BR-SA	SY-IT-TR
1	Sum of mtCO2e	47,360	335	285
2	Sum of Gigajoules	967,402	3,816	4,981
3	Intensity mt CO2e per GJ	0.0490	0.0878	0.0572
4	% Change mtCO2e previous year	-2.21%	619.02%	16.57%
5	% Change GJs previous year	-3.11%	413.22%	124.41%
6	Target Intensity	0.04663	0.08357	0.05449
7	mt CO2e per GJ Status	1	1	0

Line 3 (.0490) divides line 1 (47,360) by line 2 (967,402) to equal the Actual Intensity (.0490) of all the energy used by the facility (facilities) {Natural gas, LPG, Electricity, Diesel}

Line 6 is the Target Intensity (.04663)  – this is line 3 minus 5% [Line 1 in the TARGETtbl].

The TARGETtbl “Target” is a reduction value entered by the user. It does not come from anywhere else. It is just the desired Target for how much Intensity (line 3) should be reduced by for that Facility in that Year.

Line 6 Target Intensity is the GOAL – intensity value

Line 7 is the KPI (STATUS-red, yellow, green) based on that Measure to show how well the facility is doing relative to meeting the GOAL value (line 6).

TARGETtbl                            

Count	FacilityID	Year	Target
1	CA-IR-BA	2018	5.00%
2	SY-UK-HI	2018	5.00%
3	SY-BR-SA	2018	12.00%
4	SY-TH-SA	2018	6.00%
5	SY-IT-TR	2018	4.00%
6	SY-US-RO	2018	8.00%
7	SY-US-HA	2018	9.00%
8	SY-US-WA	2018	7.00%
9	CA-IR-BA	2019	10.00%
10	SY-UK-HI	2019	10.00%
11	SY-BR-SA	2019	10.00%
12	SY-TH-SA	2019	10.00%
13	SY-IT-TR	2019	10.00%
14	SY-US-RO	2019	10.00%
15	SY-US-HA	2019	10.00%
16	SY-US-WA	2019	10.00%

I have 2 issues: 1 major and 1 minor

MAJOR:

I can’t get the MEASURE below to work. The first part works but the multiplier should be looking into the Targettbl above FINDING the Facility and the Year and returning the Target value to be used in the formula. Note: I learned that multiplying decimals times %s to get reductions needs to be “* 1+ Target Value %”

Target Intensity:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])*1.05)

MINOR:

I use this equation “=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])” in a calculated column (in the Energytbl - not shown) to get line 3 in the pivot tbl shown above. I think it works correctly there but when I use it as part of my MEASURE above the value that it returns – in the case of facility CA-IR-BA is actually 4.76% reduction instead of 5%. I think this is a math issue – perhaps avgs. of avgs. I can live with this one if you don’t immediately see the problem.

I hope this helps you understand the situation better.

I sincerely appreciate your help!

[AlB]

@Dave-ExpSC 

MAJOR

You just need to do something similar to what we did earlier. You can get that target from the table with this measure (or just its code directly). Then you add it to 1 and you have it 

Measure2 =
LOOKUPVALUE (
    TARGETtbl[Target],
    TARGETtbl[FacilityID], SELECTEDVALUE(ENERGYtbl[FacilityID]),
    TARGETtbl[Year], SELECTEDVALUE(ENERGYtbl[Year])
    )

 Note that I'm assuming that ENERGYtbl[FacilityID] is what you have in the columns of your pivot table and you need to add the year to the pivot too (otherwise where are you specifying it?)

MINOR

Well a 5% reduction is multiplying by 0.95 which is not the same as dividing by 1.05 (1 - 1/1.05 =  0.0476, as opposed to 0.05) 

Look forward to your kudos 😉

[Dave-ExpSC]

Thanks AI,

So this is what the formula looks like.

Target =DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])*LOOKUPVALUE(TargetTBL[Target],TargetTBL[FacilityID],ENERGYtbl[FacilityID],TargetTBL[Year],ENERGYtbl[Year])

Here is the error message: The expression is not valid or appears to be incomplete. Please review and correct the expression. The end of the input was reached.

If I add another semicolon at the end I get:

Calculation error in measure 'ENERGYtbl'[Target Intensity]: A single value for column 'FacilityID' in table 'ENERGYtbl' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Something is still missing?

[Dave-ExpSC]

Hi AI,

We're almost there I hope you can find a minute to look at the formula. 

Your help is sincerely appreciated.

[Dave-ExpSC]

The answer to your question:

Note that I'm assuming that ENERGYtbl[FacilityID] is what you have in the columns of your pivot table and you need to add the year to the pivot too (otherwise where are you specifying it?).

You are correct I pull FacilityID from the EnergyTbl and need to add year too.

[Dave-ExpSC]

AIB - you were very helpful recently. I have gotten stuck on another formula. I hope you can help me out again.

I'm learning the hard way.

TARGETtbl
Count	FacilityID	Year	Target
1	CA-IR-BA	2018	10.00%
2	SY-UK-HI	2018	5.00%
3	SY-BR-SA	2018	12.00%
4	SY-TH-SA	2018	6.00%
5	SY-IT-TR	2018	4.00%
6	SY-US-RO	2018	8.00%
7	SY-US-HA	2018	9.00%
8	SY-US-WA	2018	7.00%
9	CA-IR-BA	2019	10.00%
10	SY-UK-HI	2019	10.00%
11	SY-BR-SA	2019	10.00%
12	SY-TH-SA	2019	10.00%
13	SY-IT-TR	2019	10.00%
14	SY-US-RO	2019	10.00%
15	SY-US-HA	2019	10.00%
16	SY-US-WA	2019	10.00%

ENERGYtbl
FacilityID	eSource	Year	Month	Quantity	mtCO2e	Gigajoules	mtCO2ePERGJ
CA-IR-BA	Natural Gas	2018	Jan	10,731,603	1,974	38,634	0.0511
SY-UK-HI	Natural Gas	2018	Feb	11,016,225	2,027	39,658	0.0511
SY-BR-SA	Natural Gas	2018	Mar	12,420,095	2,285	44,712	0.0511

I use this formula to calculate the mtCO2ePERGJ across multiple facilities

mt CO2e per GJ:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules]))

ENERGYtbl and TARGETtbl are both connected to the Facilitytbl with unique Facility IDs

Sum of mtCO2e	47,360	335	285
Sum of Gigajoules	967,402	3,816	4,981
Intensity mt CO2e per GJ	0.0490	0.0878	0.0572
% Change mtCO2e previous year	-2.21%	619.02%	16.57%
% Change GJs previous year	-3.11%	413.22%	124.41%
Target Intensity	0.04663	0.08357	0.05449
mt CO2e per GJ Status	1	1	0

Target Intensity is a 5% reduction: 

Problem: The reduction is hardcoded because I can't get RELATED or LOOKUPVALUE to bring the matching value into my equation

Target Intensity:=DIVIDE(sum(ENERGYtbl[mtCO2e]),SUM(ENERGYtbl[Gigajoules])*1.05)

The 1st part of this equation works.

I just need to replace the "1.05" with a formula to find the multiplier "Target" in the TARGETtbl.

It has to match FacilityID and Year in between ENERGYtbl and TARGETtbl

I tried RELATED and LOOKUPVALUE

Related tells me it can't find the relationship --- but it's there

LOOKUPVALUE - I can't get past the search value argument (it's not a specific value)

I have spent days trying solutions - learned a lot - but can't finish it.

Help would be greatly appreciated.

[Dave-ExpSC]

Oh thank you so much for solving this formula. I have tried to figure it out for days.

Now that you have solved it - I now understand the approach.

I am very grateful to you. thank you

[Dave-ExpSC]

Thank you. Close but no cigar yet. 

The formula stopped prompting on the last 2 entries. I typed in the last 2 to see what would happen. 

=LOOKUPVALUE(EFtbl[KgCO2ePER], EFtbl[FacilityID], ENERGYtbl[FacilityID], [eSource], EFtbl[facilityID], [Year], EFtbl[facilityID])

at Red "eSource" it did not prompt for tbl I presume because it is also from the Energy tbl. The last 2 items did not prompt for the right tbls and columns. I have a test file that I can send but I can't see how to attach. I greatly appreciate your help.

I got this error "A single value for column 'facilityID' in table 'EFtbl' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."