Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I'm trying to calculate the max value by using group by for multiple columns.
I Have this table :
What I want to do is to calculate the max amount for each "Id offer" by selecting the "Rev" with the maximum amount for each "Id client" as below :
If the offer has multiple "Id client" , in this case selecting the "Id client" column with the max value as below :
I'm trying this but it doesn't work :
Max amount = GROUPBY( ADDCOLUMNS( GROUPBY( Tableau_Quotation, 'Tableau_Quotation'[Document Nr], 'Tableau_Quotation'[Rev], ), "Max Count", CALCULATE(MAX(Tableau_Quotation[Offer amount EUR])) ), 'Tableau_Quotation'[Document Nr], "Max", MAXX(CURRENTGROUP(), [Max Count]))
How I can fix that with dax ?
Thanks in advance
Solved! Go to Solution.
@K-LED ,
If I understand this correctly, you are creating a new table. I used SUMMARIZE and LOOKUPVALUE
new = SUMMARIZE('Table','Table'[ID Offer],'Table'[ID Client],"AMT",max('Table'[Amount])), == Lookup = LOOKUPVALUE('Table'[Rev],'Table'[Amount],new[AMT],'Table'[ID Offer],new[ID Offer],'Table'[ID Client],new[ID Client])
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Will post pbix shortly.
Proud to be a Super User!
@K-LED ,
If I understand this correctly, you are creating a new table. I used SUMMARIZE and LOOKUPVALUE
new = SUMMARIZE('Table','Table'[ID Offer],'Table'[ID Client],"AMT",max('Table'[Amount])), == Lookup = LOOKUPVALUE('Table'[Rev],'Table'[Amount],new[AMT],'Table'[ID Offer],new[ID Offer],'Table'[ID Client],new[ID Client])
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Will post pbix shortly.
Proud to be a Super User!
Hi,
Thanks for your response @Nathaniel_C ,
If it's possible I'd like to not create a new table but creating a new measure at the same table.
Otherwise with your solution you miss the last step by selecting the "Id offer" with the max "AMT":
Id Offer | ID Client | AMT | Lookup
1 | B | 150 | 2
Can we use the same functions without creating a new table but stay with the same table ?
Thnak you again for your answer!
Hi @K-LED ,
Maybe I am not seeing for what you are looking.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Aren't these the same?
Proud to be a Super User!
Hii,
I added a step to select the line with the max amount...
Thank you again for your answer
Regards.
You are welcome!
Proud to be a Super User!
PBIX file PBIX
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |