cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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] ) )

View solution in original post

4 REPLIES 4
Highlighted
Super User III
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  Datanaut

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
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] ) )

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Max per category in a column

Many thanks!

Problem solved!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors