Today we are going to discuss about Grouping the data to determine maximum and minimum sales for all zones.
Our dataset includes the Zone and Sales columns, and our goal is to group the zones and determine the maximum and minimum sales.
Method-1:
Formula in D2 cell,
=LET(a,GROUPBY(A2:A20,B2:B20,HSTACK(MAX,MIN),,0),IF(a="","Zone",a))
The GROUPBY function in Excel is most appropriate for our situation.
The arguments for this function are:
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
All arguments except the first three are optional.
In the row_fields argument, reference the Zone column,
in the values argument, reference the Sales column,
in the function argument,
Since we need to create two columns for maximum and minimum, use the HSATCK function to stack them horizontally and apply the MAX and MIN functions.
Our current output has a blank cell at D2. To replace this blank cell, we can use the IF Function to replace it with "Zone".
Method-2:
=LET(a,A2:A20,b,B2:B20,c,SORT(UNIQUE(a)),VSTACK({"Zone","Max","Min"},HSTACK(c,MAXIFS(b,a,c),MINIFS(b,a,c))))
Variable a contains the Zone column,
b contains the Sales column,
c contains the sorted unique list of the Zone column.
{"East";"North";"South";"West"}
=HSTACK(c,MAXIFS(b,a,c),MINIFS(b,a,c))
The HSTACK function allows us to stack the columns horizontally,
where the first column is c, which is the sorted unique list of the Zone column.
for the second column, to determine the maximum sales, we can use the MAXIFS function,
Arguments of the MAXIFS function,
=MAXIFS(max_range,criteria_range,criteria,...)
in this context,
max_range refers to the Sales column,
criteria_range refers to the Zone column, and
criteria are the sorted unique list of zones.
This will deliver us the maximum sales of 4 zones.
Similarly, we can determine minimum sales using the MINIFS function.
The VSTACK function assists us in stacking column headers.
Method-3:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.Sort(Table.Group(Source,"Zone",
{
{"Max", each List.Max(_[Sales])},
{"Min", each List.Min(_[Sales])}
}),"Zone")
in
Result
The Source step allows us to import the Table into the Power Query Editor.
In the Result step,
the Table.Group function operates similarly to the GROUPBY function in Excel, offering greater flexibility.
It includes 5 arguments, as outlined below,
For table argument feed source step, which is our input table,
The Key argument enables us to select the column for grouping purposes.
The aggregatedColumns argument allows us to create multiple columns in a nested list format.
In Max Column,
each _ is the custom function that maintains a table for each zone after grouping. This table exclusively contains records for that specific zone. To select the Sales column, we can use square brackets [], which serve as field access operators. This will present the sales column as a list, and the List.Max function will assist in identifying the highest sales value.
We can perform the same calculations for minimum sales, but we must use the List.Min function.
Comments