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
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 |
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