topic Calculated max value by using group by for multiple columns in DAX Commands and Tips

Calculated max value by using group by for multiple columns

Anonymous — Mon, 21 Oct 2019 15:23:16 GMT

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

Re: Calculated max value by using group by for multiple columns

Nathaniel_C — Mon, 21 Oct 2019 17:37:07 GMT

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.

Re: Calculated max value by using group by for multiple columns

Nathaniel_C — Mon, 21 Oct 2019 17:38:55 GMT

PBIX file PBIX

Re: Calculated max value by using group by for multiple columns

Anonymous — Tue, 22 Oct 2019 06:19:56 GMT

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!

Re: Calculated max value by using group by for multiple columns

Nathaniel_C — Tue, 22 Oct 2019 13:38:52 GMT

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?

Re: Calculated max value by using group by for multiple columns

Anonymous — Tue, 22 Oct 2019 13:42:08 GMT

Hii,

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

Thank you again for your answer

Regards.

Re: Calculated max value by using group by for multiple columns

Nathaniel_C — Tue, 22 Oct 2019 13:50:42 GMT

You are welcome!