Calculate the max value by category

[syelify]

I have this table (left) and showing the result should be.

It will sum all the maximum shelves w

Any help will appreciated, thanks in advance!

[ryan_mayu · Accepted solution]

@syelify 

you can do the data transform in PQ

1. change date to the first day of month

2. group by data

then you can get the output

However, the output is different from yours. 

could you pls explain why you don't count 63-65 to Jan?

[syelify · Accepted solution]

Thank you, would it be possible to have the Dax measure

[ryan_mayu · Accepted solution]

create two columns

month = FORMAT('Table'[Date],"mmm")

shelves2 = if('Table'[Shelves]=CALCULATE(max('Table'[Shelves]),ALLEXCEPT('Table','Table'[month],'Table'[Door])),'Table'[Shelves],0)

[syelify]

Thank you 🙂

[Anonymous · Accepted solution]

Hi @syelify 

Thanks to @ryan_mayu  for your timely and effective response, here are my additions:

You can create two measures as follow.

max = 
VAR _month = MONTH(MAX([Date]))
VAR _max = CALCULATE(MAX([Shelves]), FILTER(ALLEXCEPT('Table', 'Table'[Door]), MONTH([Date]) = _month))
RETURN
_max

sum = CALCULATE(SUM('Table'[Shelves]), FILTER('Table', [Shelves] = [max]))

Change Date to a hierarchical structure

Output:

Best Regards,
Yulia Xu

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

[syelify · Accepted solution]

Thank you, would it be possible to have the Dax measure

[ryan_mayu · Accepted solution]

create two columns

month = FORMAT('Table'[Date],"mmm")

shelves2 = if('Table'[Shelves]=CALCULATE(max('Table'[Shelves]),ALLEXCEPT('Table','Table'[month],'Table'[Door])),'Table'[Shelves],0)

[ryan_mayu · Accepted solution]

@syelify 

you can do the data transform in PQ

1. change date to the first day of month

2. group by data

then you can get the output

However, the output is different from yours. 

could you pls explain why you don't count 63-65 to Jan?