Forum Discussion
Calculate sum Max
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 |
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.
4 Replies
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
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.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.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.
