Forum Discussion
DAX
Hi ren
What I did was to load the data in Power Query, and then merge the data together on the "Tender Number"
What I did then was to create a new calculated column which gave me the Max Value across the 3 values
Max Value = MAX(MAX('Merge1'[Award Value],'Merge1'[Contract Value]),'Merge1'[Extended Total Amount])Which resulted in the final column in the table displaying the data as required.
You can find the link here to the PBIX: https://1drv.ms/u/s!Apxn-69XhcAmiMITsW8F0_YUJPrZgg
Do you have some sample data that could be used?
As well as an expected output that could be worked towards.
EIdeal format would be one column with the corresponding $ Value that is highest of the three locations for each tender number.
- Thanks for that, but the data is all in one line. If it would be possible to have it formatted so that it is in a table?
Sorry, it actually was and posted in a single line. Trying again.
Sheet 1
Tender Number
Company Name
Award Value
F14-0014
Marys Hats
$4,908,918.15
F15-0011
Marys Hats
$509,9061.28
F15-0012
Marys Hats
$110,110.05
F15-0013
Marys Hats
$325,623.22
F15-0014
Marys Hats
$7,205,123.00
F15-0015
Marys Hats
$4,704,191.54
Sheet 2
Tender Number
Closing Date
Trading As
Extended Total Amount
Contract No
Contract Value
F14-0014
4/03/2015
Bobs dogs
F14-0014
$17,000,000
F15-0011
23/07/2015
Bobs cats
F15-0011
$890,595.00
F15-0012
20/05/2015
Bobs trees
$4,908,918.15
F15-0012
$432,356.00
F15-0013
20/05/2015
Bobs Bob
$5,099,061.28
F15-0013
F15-0014
20/05/2015
Bob Pink
$4,332,609.08
F15-0014
F15-0015
20/05/2015
Bob Black
$4,704,191.54
F15-0015
$4,704,191.54