Calculate sum of the max values (show in rows) in a subtotal/total

[FloUL]

Hi, please anyone know how to show, in the total/subtotal of a specific column (in my case column "Max de Inscrits") the sum of the max values showed in each row of a matrix ? I read several posts but still can't resolve this.

Column "Max de Inscrits" is an aggregate (maximum values).

As shown in picture below, instead of showing 10, I would like the total to show the sum of rows values (6+2+3+3+10) which is 24

In my case, the subtotal "Directeur de recherche" should also show 24 instead of 10.

Thank you in advance !

Flora

[edhans · Accepted solution]

You must create a measure to do this @FloUL. This is one way:

Max Sales =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Sales,
            Sales[Customer]
        ),
        "MaxCustomer",
            CALCULATE(
                MAX( Sales[Value] )
            )
    ),
    [MaxCustomer]
)

If you need more help, please provide some data to work with.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

By the way, this is the fake data I created so you can see it is pulling the MAX of each customer.

[developer-yit2]

Hello @edhans , this solution is working like a charm. Thank you. 
However, this doesn't work when I select only the month from the slicer. So, I have three slicers, One for dates, one for the months, and one for years. The thing is when I select the days, it's giving me an accurate sum but when I select only a Month or a year, it gives me an inaccurate sum. These slicers are using the same date column and I have used the hierarchy of Months in the second and the hierarchy of years from the date column in the third slicers. So, basically, these three slicers are using the same date column but with hierarchies 

How to resolve this? 

Thank you!

Regards,
Majid Ali

[edhans]

Hi @developer-yit2 

you should probably create a new thread/question for this as this question was answered over 2 years ago, and provide sufficent information. Instructions on doing that are below.


Hierarchies are not true stuctures in the data, so if you select January in your slicers above and nothing in Year, it will aaggregate all of the January's together across 2022, 2021, and 2020, which is usually nonsensical unless you are looking at annual sales trends.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[developer-yit2]

Hello,
Thank you @edhans  for a quick response. 
Yes, you are right I need to select the year but even when I select a year, it just shows the sum of each month's max value. and If I select a month, it just shows the sum of each day's max value.
I have created another thread. Please let me know your thoughts over there. 
https://community.powerbi.com/t5/Desktop/Calculate-sum-of-Max-values-in-a-matrix-row-total/td-p/2812... 

[FloUL]

The approach you described @edhans is exact and work like a charm !

I applied it and now the result show 24. Thank you !

[edhans]

Great @FloUL - consider that a DAX pattern for future use. Also look at the links @Greg_Deckler sent which are a bit more generic, but go into more detail on the common "my totals aren't adding up the values in the table" problem. 😊

Glad I was able to help!

[Greg_Deckler]

@FloUL - Hard to tell but here are a number of things to read:

Could be a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Could be a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Or you might need MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[FloUL]

I went trough the information and links you provided @Greg_Deckler , they are thorough and extremely helpful !

I'm seeing solution on similar concerns I had with other matrix.

Thank you 🙂