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.
Just started learning DAX and need help acheiving the following query.
I need to match the Tender # from Sheet 1, Column Y with, corresponding Tender # on Sheet 2, Colum X. Then I will have 3 Contract values and I need the largest of the 3 values to be in a particular column and row corresponding to the tender # for which the value is largest.
IF = ( Sheet1Tender# appears in Sheet2Column X ([Sheet1Value A] > [Sheet1Value B] = [Sheet1Value A]) or (If = [Sheet1Value B] > [Sheet1Value A] = [Sheet1Value B]) or ([Sheet2Value C > [Sheet1Value A] & [Sheet1Value B]))
Solved! Go to Solution.
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
EIdeal format would be one column with the corresponding $ Value that is highest of the three locations for each tender number.
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
Thanks Gilbert!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |