Forum Discussion
max and min value from group
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?
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
In the Group By dialog, select Advanced Mode and add the Min aggregation:
2 Replies
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
In the Group By dialog, select Advanced Mode and add the Min aggregation: