MAX value of summarized values based type per month

[matdax]

Hello. 

I have a scenario in a table where I have a values column, a type of values column, location, and date period. The value being presented is not a contributory value but the total result.

See below as an example

year	month	type	location	value
2024	august	total dogs	chicago	50
2024	july	total dogs	toronto	60
2024	august	total dogs	toronto	40
2024	july	total dogs	chicago	10
2023	august	total dogs	chicago	50
2023	july	total dogs	toronto	80
2024	august	total cats	chicago	10
2024	july	total cats	toronto	10
2023	august	total cats	chicago	10

What I am trying to do, is add up the total dogs (disregarding location) for each month, and then display the max number of dogs (type column) per period. Each month should only have one entry per location per type. However if someone errornously enters 2 values, the max value for that month should be used.

Expected result: august 2024 = 90 dogs
For the year of 2024 = 90 dogs as august is the higher number

For July 2024 = 70 dogs

Would love some help!

[Anonymous]

Hi @matdax ,

I made simple samples and you can check the results below:

Total = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[type],'Table'[year],'Table'[month]))    

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

However if someone errornously enters 2 values, the max, or last value for that month should be used.

Power Query does not know what you mean by "last".  You need to provide your own sort column.

[matdax]

Thanks! Almost perfect

Can a filter be included within the measure to only return this data for total dogs?

[lbendlin]

Not sure what you are asking. How to use filters in Power BI?

[matdax]

No I realise I could apply the filter to the visual this is attached to or to the report, but for this purpose I want the data in the column itself to exclusively have the dogs. I tried adjusting the filter as part of var c, but it didn't work

[lbendlin]

I tried adjusting the filter as part of var c, but it didn't work

That's the right spot.  Show what you tried.

[matdax]

Thanks again for your help, here's what I have

Max count =

var a = SUMMARIZE('Table',[year],[month],[location])

var b = ADDCOLUMNS(a,"i",CALCULATE(max('Table'[Index])))

var c = FILTER('Table',[Index] in SELECTCOLUMNS(b,[i]) && 'Table'[type] == "total dogs")

var d = SUMMARIZE(c,[year],[month])

var e = ADDCOLUMNS(d,"ct",CALCULATE(sum('Table'[value])))

RETURN MAXX(e,[ct])

Displaying the value on its own table still shows 100, vs 90

[lbendlin]

Try this

var c = FILTER('Table',[Index] in SELECTCOLUMNS(b,[i]) && [type] = "total dogs")

[matdax]

Same result - it returns 100 for August, whilst it should report 90

[lbendlin]

Cute.  Need to investigate it a bit more.  In the meantime you can cheat by using a visual filter

[matdax]

Thanks! Yeah it's got me scratching my head

[SachinNandanwar]

What value should be displayed for cats, or should the records for cats be discarded?

[matdax]

We may want to acheive the same result as per dogs for the cat data, but it isn't neccesary. For the purposes of what I'm after here, cats can be disregraded thank you