Dynamic Lookup Value

[Anonymous]

Hello all, 

I have a table and I am creating columns to find the nth highest average costs based on certain filters. 

So I am finding the Highest Cost, the second highest cost and then the third highest cost. 

Now... for each of these "highest costs" I am trying to lookup the corresponding value in a specific column

Bascially dax finds the 2nd highest cost (in a specific column) and i need it to find the corresponding value on the same row but in another column || ALL this with the same filters. 

I understand the result will be duplicated but that's ok. 

Basically, the result in Top Cost #1 Loads should be = 2. 

I am filtering on 3 columns  (using earlier to do it dynamically) to obtain this subset of the tables - that I am replicating in my Dax formulas. 

[v-yuta-msft]

@Anonymous ,

Could you please share sample data and give the expected result?

Regards,

Jimmy Tao

[Anonymous]

Here - let me know if that helps

Filter 1		Filter 2	Filter 3	Data	Data	Completed	Completed	Completed	Need help	Need help	Need help	Completed	Completed	Completed	Completed	Need Help	Need Help	Need Help	Completed	Completed
Region	Carrier	Time period	Equipment Type	Avg Cost	Count of Loads	Top Cost 1	Top Cost 2	Top cost 3	Top Cost 1 # loads	Top Cost 2 # loads	Top Cost 3 # loads	WACL	Top Loads 1	Top Loads 2	Top Loads 3	Top Loads Cost 1	Top Loads Cost 2	Top Loads Cost 3	WACL	Savings
1	Carrier 1	19	Truck 1	309.4	3	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 2	19	Truck 1	364.4	2	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 3	19	Truck 1	363.9	63	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 4	19	Truck 1	292.7	31	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 5	19	Truck 1	291.7	65	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 6	19	Truck 1	160.1	1	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 7	19	Truck 1	312.2	6	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 8	19	Truck 1	299.6	9	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 9	19	Truck 1	394.1	12	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
1	Carrier 10	19	Truck 1	77.0	1	394.1	364.4	363.9	12	2	63	368.6	65	63	31	291.7	363.9	292.7	320.5	3,703.9
									Corresponding # of loads of the highest average cost	Corresponding # of loads of the 2nd highest average cost	Corresponding # of loads of the 3rd highest average cost					Similar logic but need corresponding average cost for the highest number of loads	... for 2nd highest number of loads	... for 3rd highest number of loads

[danextian]

Could you please post a sample data (not an image)?

[Anonymous]

Posted can you help please. ?