Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic Lookup Value

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 th...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

Anonymous ,

 

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

 

Regards,

Jimmy Tao

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Here - let me know if that helps

     

    Filter 1 Filter 2Filter 3DataDataCompletedCompletedCompletedNeed helpNeed helpNeed helpCompletedCompletedCompletedCompletedNeed HelpNeed HelpNeed HelpCompletedCompleted
    RegionCarrierTime periodEquipment TypeAvg CostCount of LoadsTop Cost 1Top Cost 2Top cost 3Top Cost 1 # loadsTop Cost 2 # loadsTop Cost 3 # loadsWACLTop Loads 1Top Loads 2Top Loads 3Top Loads Cost 1Top Loads Cost 2Top Loads Cost 3WACLSavings
    1Carrier 119Truck 1              309.43              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 219Truck 1              364.42              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 319Truck 1              363.963              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 419Truck 1              292.731              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 519Truck 1              291.765              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 619Truck 1              160.11              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 719Truck 1              312.26              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 819Truck 1              299.69              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 919Truck 1              394.112              394.1              364.4              363.912263              368.6656331              291.7              363.9              292.7              320.5          3,703.9
    1Carrier 1019Truck 1                77.01              394.1              364.4              363.912263              368.6656331              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