topic Re: Sum the values only for the MAX category by month in DAX Commands and Tips

Sum the values only for the MAX category by month

MarcoSilva — Thu, 23 Feb 2023 20:34:56 GMT

Hello!

I'm having trouble to create a DAX measure where I need to sum only the values of the lines where the category is the max category of the month.

Below is the sample data:

The measures I created are these:

SumCategories =

CALCULATE(

SUM([Value]),

FILTER(Sheet1, [Type] = "A"),

FILTER(Sheet1, [Category] = MAX(Sheet1[Category]))

)

And this one where I get the accumulated value by month:

SumCategoriesYTD =

TOTALYTD(

[SumCategories],

Sheet1[Date],

"09/30"

)

I also created a measure to check what is the max category of the month:

MaxCategory = MAX(Sheet1[Category])

The result is below:

The values in the SumCategories and the MaxCategory for each month are ok, but the totals and the SumCategoriesYTD are terribly wrong. Anyone can help me with that?

Here are the expected values:

Thanks in advance.

Re: Sum the values only for the MAX category by month

Greg_Deckler — Thu, 23 Feb 2023 20:44:06 GMT

MarcoSilva First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like 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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Sum the values only for the MAX category by month

MarcoSilva — Fri, 24 Feb 2023 02:31:09 GMT

Hi Greg_Deckler, thanks for the contribution.

It helped me to better understand the problem but yet I can't figure out how to resolve it.

Anyone else?

Re: Sum the values only for the MAX category by month

andhiii079845 — Fri, 24 Feb 2023 11:01:12 GMT

Hi,

try this DAX

SumCategories_new = 

Var tbl =  SUMMARIZE(Tabelle4,Tabelle4[Date],"sumcategorie",[SumCategories])

RETURN sumx(tbl,[sumcategorie])

You have to change the Table name in the formular.

If you want MaxCategory without total try this:

MaxCategory = IF(HASONEFILTER(Tabelle4[Date]),MAX(Tabelle4[Category]),BLANK())

A good link: https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

Re: Sum the values only for the MAX category by month

MarcoSilva — Fri, 24 Feb 2023 14:09:15 GMT

Hi andhiii079845!

Your contribution worked perfectly!

Thank you very much.