Forum Discussion
Return Single Value after Selection
Greetings -- would appreciate help understanding in PBI, what would be a Fixed calculation in Tableau. (I've reviewed some posts here but not understanding).
In the data set sample below, what's happening is I have a set of ID's (1 and 2), each w/an associated SCAC and Cost and Code. I'm building a table that will show, by ID, the lowest Total Cost and SCAC. That part I have. What I can't seem to find is how to return just the Code that is associated w/the ID/SCAC combination that is the lowest Total Cost. What would that calculation look like?
Thank you for any help on this.
| ID | SCAC | Total Cost | Code | ID | LR SCAC | Total Cost | Code | |
| 1 | SAIA | $ 416.66 | 2 | 1 | RDFS | $ 355.23 | 4 | |
| 1 | RDFS | $ 355.23 | 4 | 2 | ABFSN | $ 399.16 | 3 | |
| 1 | EXLA | $ 473.34 | 2 | |||||
| 1 | CTII | $ 490.86 | 2 | |||||
| 1 | CNWY | $ 506.25 | 3 | |||||
| 1 | UPGF | $ 519.22 | 3 | |||||
| 1 | ABFS | $ 565.63 | 3 | |||||
| 1 | AACT | $ 601.95 | 2 | |||||
| 1 | RLCA | $ 720.90 | 2 | |||||
| 1 | ODFL | $ 1,420.25 | 2 | |||||
| 1 | SAIA2 | $ 487.58 | 2 | |||||
| 1 | EXLA2 | $ 442.42 | 0 | |||||
| 1 | FWRA | $ 427.27 | 2 | |||||
| 1 | FWRAC | $ 370.62 | 2 | |||||
| 1 | ABFSC | $ 775.56 | 3 | |||||
| 1 | CNWYC | $ 817.16 | 3 | |||||
| 1 | ABFSN | $ 663.97 | 3 | |||||
| 2 | CNWY | $ 531.96 | 2 | |||||
| 2 | UPGF | $ 465.86 | 1 | |||||
| 2 | RLCA | $ 547.51 | 2 | |||||
| 2 | DAFG | $ 472.97 | 2 | |||||
| 2 | EXLA | $ 483.62 | 2 | |||||
| 2 | SAIA | $ 664.78 | 2 | |||||
| 2 | ABFS | $ 571.09 | 3 | |||||
| 2 | ODFL | $ 893.09 | 1 | |||||
| 2 | EXLA2 | $ 433.82 | 0 | |||||
| 2 | SAIA2 | $ 478.93 | 2 | |||||
| 2 | CNWYC | $ 559.38 | 2 | |||||
| 2 | ABFSC | $ 455.32 | 3 | |||||
| 2 | FWRAC | $ 1,875.54 | 0 | |||||
| 2 | ABFSN | $ 399.16 | 3 |
Hi WorldWide1 ,
I assume when you refer to a table you mean a visualization create the following 3 measures:
LR SCAC = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[SCAC]) Total Cost Value = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[Total Cost]) Code Value = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[Code])Now add the ID to a table and the measure you should get the following result.
2 Replies
Hi WorldWide1 ,
I assume when you refer to a table you mean a visualization create the following 3 measures:
LR SCAC = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[SCAC]) Total Cost Value = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[Total Cost]) Code Value = MINX(TOPN(1, 'Table', 'Table'[Total Cost], ASC), 'Table'[Code])Now add the ID to a table and the measure you should get the following result.
Perfect - thank you!
