topic Re: MAX value of summarized values based type per month in DAX Commands and Tips

MAX value of summarized values based type per month

matdax — Thu, 19 Sep 2024 00:34:02 GMT

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!

Re: MAX value of summarized values based type per month

SachinNandanwar — Wed, 18 Sep 2024 23:53:26 GMT

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

Re: MAX value of summarized values based type per month

lbendlin — Thu, 19 Sep 2024 00:14:02 GMT

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.

Re: MAX value of summarized values based type per month

matdax — Thu, 19 Sep 2024 00:34:49 GMT

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

Re: MAX value of summarized values based type per month

Anonymous — Thu, 19 Sep 2024 05:42:54 GMT

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.

Re: MAX value of summarized values based type per month

matdax — Thu, 19 Sep 2024 06:00:57 GMT

Thanks! Almost perfect

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

Re: MAX value of summarized values based type per month

lbendlin — Thu, 19 Sep 2024 11:24:24 GMT

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

Re: MAX value of summarized values based type per month

matdax — Thu, 19 Sep 2024 22:23:09 GMT

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

Re: MAX value of summarized values based type per month

lbendlin — Thu, 19 Sep 2024 22:49:12 GMT

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

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

Re: MAX value of summarized values based type per month

matdax — Sun, 22 Sep 2024 22:50:00 GMT

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

Re: MAX value of summarized values based type per month

lbendlin — Sun, 22 Sep 2024 23:24:59 GMT

Try this

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

Re: MAX value of summarized values based type per month

matdax — Sun, 22 Sep 2024 23:49:51 GMT

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

Re: MAX value of summarized values based type per month

lbendlin — Mon, 23 Sep 2024 00:11:24 GMT

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

Re: MAX value of summarized values based type per month

matdax — Mon, 23 Sep 2024 22:32:09 GMT

Thanks! Yeah it's got me scratching my head