cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## Max per category in a column

Hi Community,

i'm struggling with a seemingly simple problem :).

I have a column with Items (Item ID), multiple item ID's possible. Next to that a calculated column with count rows:

Item ID   Count rows

H32WB    3

H32WB    5

A78VB     27

A78VB    36

I would like to create a new calculated column which displays the highest value from count row. But the highest per Item ID. So for example the highest for Item H32WB would be 5, for A78VB 36 and so on. Many thanks for your help :).

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Max per category in a column

for a calculated column:

```NewCol =
CALCULATE ( MAX ( Table1[Count rows] ), ALLEXCEPT ( Table1, Table1[Item ID] ) )
```
4 REPLIES 4
Highlighted
Super User III

## Re: Max per category in a column

Hi @Anonymous

1. Place ItemID in the rows of a table visual

2. Create a simple measure and place it in the visual

`Measure = MAX(Table1[Count rows])`

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Highlighted
Anonymous
Not applicable

## Re: Max per category in a column

Hi,

many thanks for the reply. But that will not give me the max per category, just the max for the column.

Further I need a solution within a calculated column not measure :).

If my question was not clear.

In column Item ID's are double Item ID's, count row indicates the counted rows (lines). I need the max counted row per Item ID. So if there are for example 2 item IDs with ID ABCD123, one with value 4 and one with value 5 -> I need to see the max value in this case 5.

Highlighted
Super User III

## Re: Max per category in a column

for a calculated column:

```NewCol =
CALCULATE ( MAX ( Table1[Count rows] ), ALLEXCEPT ( Table1, Table1[Item ID] ) )
```
Highlighted
Anonymous
Not applicable

## Re: Max per category in a column

Many thanks!

Problem solved!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021