Sum the values only for the MAX category by month

[MarcoSilva]

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.

[andhiii079845 · Accepted solution]

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

[MarcoSilva]

Hi @andhiii079845!

Your contribution worked perfectly!

Thank you very much.

[MarcoSilva]

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?

[Greg_Deckler]

@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