max and min value from group

[scsos]

So I figured how to group values but now I'm trying to get max and min of those values for every person.

I've worked out how to group and get max but then I end up with just the max and all the other values are gone, when I want to get the min for each person too.  How to do that?

[PhilipTreacy · Accepted solution]

@scsos 

Download example PBIX file with the code below

I’m not 100% sure I know what you mean but if you are asking to get both the max and min value for each person and end up with just 1 row per person and a column each for max and min, like this

Then you can do it this way.

First, group by Person and for the Operation choose All rows

Giving this

You can access the values inside the tables by using this code, create a new Custom Column

= List.Min([All][Amount])

Likewise to get the max use List.Max

= List.Max([All][Amount])

You can delete the column with the Tables to give the result

Regards

Phil

[ronrsnfld · Accepted solution]

In the Group By dialog, select Advanced Mode and add the Min aggregation:

[PhilipTreacy · Accepted solution]

@scsos 

Download example PBIX file with the code below

I’m not 100% sure I know what you mean but if you are asking to get both the max and min value for each person and end up with just 1 row per person and a column each for max and min, like this

Then you can do it this way.

First, group by Person and for the Operation choose All rows

Giving this

You can access the values inside the tables by using this code, create a new Custom Column

= List.Min([All][Amount])

Likewise to get the max use List.Max

= List.Max([All][Amount])

You can delete the column with the Tables to give the result

Regards

Phil