Calculated max value by using group by for multiple columns

[Anonymous]

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

[Nathaniel_C · Accepted solution]

@Anonymous ,

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.

[Anonymous]

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!

[Nathaniel_C]

Hi @Anonymous ,

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?

[Anonymous]

Hii,

I added a step to select the line with the max amount...

Thank you again for your answer

Regards.

[Nathaniel_C]

You are welcome!

[Nathaniel_C]

PBIX file PBIX