Calculate sum Max

[Anonymous]

Hello, Help me please,

I'd like to calculate the maximum values of a table by municipality and month, and then sum up the maximum values obtained.
The data is sorted by as shown in the image

Mois	commune	donnée
Janvier	A	5
Janvier	B	2
Janvier	C	3
Fevrier	A	6
Fevrier	B	8
Fevrier	C	1
Mars	A	0
Mars	B	3
Mars	C	9

[SamInogic]

Hi @Anonymous ,

You need to create a calculated column to the maximum value for each municipality and month combination with below DAX expression,

MaxValue =

VAR CurrentCommune = Table1[commune]

VAR CurrentMonth = Table1[Mois]

RETURN

    CALCULATE(

        MAXX(

            FILTER(

                Table1,

                Table1[commune] = CurrentCommune && Table1[Mois] = CurrentMonth

            ),

            Table1[donnée]

        )

    )

Then create a measure that calculates the sum of the maximum values obtained for each municipality and month with below DAX expression:

TotalMaxValue = SUMX(Table1, Table1[MaxValue])

Thanks!

Inogic Professional Services

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

I want to do this:
Create a column that searches for maximum values by commune and month.
Then sum the maximums obtained.
If for a commune A I have in January 7, in February 2 and in March 6, the formula should look for the maximum of values for commune A per month. In this case, the new column should give 7. and so on until I have all the maximum data per commune and then add them together.

[Anonymous · Accepted solution]

Hi, @Anonymous 

According to your description, I created the following sample data and wrote a calculated column of DAX expressions based on these data:

DAX formula:

Column =
VAR _name = 'Sheet4 (3)'[commune]
VAR _table =
    FILTER ( 'Sheet4 (3)', 'Sheet4 (3)'[commune] = _name )
RETURN
    CALCULATE ( MAXX ( _table, 'Sheet4 (3)'[donnée] ) )

The result is as follows:

In the scenario of Commune A, the biggest value of finding each month is 6. I provided the pbix file used this time. If the above DAX can help you, it is great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @SamInogic 

Thanks for the suggestion, but I'm still not getting the desired result.
When I use the given syntax, I get the sum of all the data in the column.
Normally, with the data example, I'd get 23 as the result, but here I get 37.