topic Re: Calculate sum of the max values (show in rows) in a subtotal/total in DAX Commands and Tips

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

FloUL — Thu, 08 Oct 2020 16:49:47 GMT

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

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

Greg_Deckler — Thu, 08 Oct 2020 17:45:02 GMT

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-Roll/m-p/411443#M150

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.

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

edhans — Thu, 08 Oct 2020 17:55:12 GMT

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.

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

FloUL — Thu, 08 Oct 2020 18:55:30 GMT

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

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

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

edhans — Thu, 08 Oct 2020 20:20:36 GMT

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!

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

FloUL — Fri, 09 Oct 2020 13:58:29 GMT

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 🙂

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

developer-yit2 — Sun, 02 Oct 2022 23:09:54 GMT

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

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

edhans — Sun, 02 Oct 2022 23:22:39 GMT

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.

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

developer-yit2 — Mon, 03 Oct 2022 00:34:00 GMT

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/2812536

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

cc1026ly — Wed, 31 Jul 2024 06:35:00 GMT

edhans

I have the same problem and thank you it works well for the subtotal

however I am wondering if we can create a subtotal by 2 different column?

in the case FloUL provided,

the solution can get the subtotal of "Directeur de recherche"

any solution can also get the subtotal of "Doctorat"?