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.
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.
@Anonymous ,
Could you please share sample data and give the expected result?
Regards,
Jimmy Tao
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 |
Could you please post a sample data (not an image)?
Proud to be a Super User!
Posted can you help please. ?
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |