Forum Discussion
GroupBy function
I am trying to use GroupBy function along with SumX and AverageX to get to particular result.
Let me explain it using a table
Year Country Item Category Amount
2021 USA Iron C1 0
2021 USA Iron C2 0
2021 USA Iron C3 0
2021 USA Iron C4 0
2021 USA Iron C5 0
2021 USA Iron C6 0
2021 USA Iron C7 0
2021 USA Iron C8 1
2021 USA Iron C9 0
2021 USA Iron C10 0
2021 USA Iron C11 0
2021 USA Iron C12 0
2021 UK Iron C1 1
2021 UK Iron C2 1
2021 UK Iron C3 0
2021 UK Iron C4 0
2021 UK Iron C5 0
2021 UK Iron C6 0
2021 UK Iron C7 0
2021 UK Iron C8 1
2021 UK Iron C9 0
2021 UK Iron C10 0
2021 UK Iron C11 0
2021 UK Iron C12 0
Category 1 to 4 makes group 1
Category 5 to 8 makes group 2
Category 9 to 12 makes group 3
Now based on the above Group we need to add the categories, for Group 1 it will be Category 1+ Category 2+ Category 3 + Category 4 and hence the value for
Group1 = 0
Group 2 = 1
Group 3 = 0
After this, we need to Average these group = Average (Group 1, Group 2, Group 3) and the answer will be 0.33 & 1
Year Country Item Value
Final Output = 2021 USA Iron 0.33
Final Output = 2021 UK Iron 1
Post this we need to rank them based their value, so uk will be rank 1 & US will be 2
Any help will be highly appreciated.
Hi Diptarup
Sorry I don't know how to modify the Rank measure...😅 But I found a workaround to get the same result. I summarize data into a new table with DAX, then add a rank column to this table. Just like below.
Create a new table:
RankTable = ADDCOLUMNS(SUMMARIZE('Table','Table'[Year],'Table'[Country],'Table'[Item]),"Average_of_Group",[Average of Group])Create a new column:
Index = RANKX('RankTable',[Average_of_Group],,DESC,Dense)At last, use columns from this RankTable to create a table visual.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
6 Replies
Diptarup , You need to create a new column
Switch(True(),
[Category] <=4, "Group 1",
[Category] <=8, "Group 2",
"Group 3"
)
Thanks for the reply. Could you please elaborate on the next steps after I created the group so to achieve the value 0.33.
Also Group 1 and Group 2 overlap, because the condition is less than and equal to 8, which also include 1 to 4.
Hi Diptarup
To add a group column, you can right click on Category column in Fields pane and select New group (Use grouping and binning in Power BI Desktop). Or you can also create a new column with below DAX code:
Column = VAR categoryNumber = VALUE(RIGHT('Table'[Category],LEN('Table'[Category])-1)) RETURN SWITCH(TRUE(), categoryNumber<=4, "Group 1", categoryNumber<=8, "Group 2", "Group 3" )Then create measures:
Average of Group = VAR groups = DISTINCTCOUNT('Table'[Group]) RETURN DIVIDE(SUM('Table'[Amount]),groups)Rank No. = RANKX('Table',[Average of Group],,DESC)Put them in a table visual.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Hi v-jingzhang,
Many thanks for helping with the query. The Average for the group worked fine. However the problem is with the ranking function. I have added few more data lines to the existing table for your refernce.
Looking forward for your assistance in resloving this.
Year Country Item Category Amount
2021 USA Iron C1 0
2021 USA Iron C2 0
2021 USA Iron C3 0
2021 USA Iron C4 0
2021 USA Iron C5 0
2021 USA Iron C6 0
2021 USA Iron C7 0
2021 USA Iron C8 1
2021 USA Iron C9 0
2021 USA Iron C10 0
2021 USA Iron C11 0
2021 USA Iron C12 0
2021 UK Iron C1 1
2021 UK Iron C2 1
2021 UK Iron C3 0
2021 UK Iron C4 0
2021 UK Iron C5 0
2021 UK Iron C6 0
2021 UK Iron C7 0
2021 UK Iron C8 1
2021 UK Iron C9 0
2021 UK Iron C10 0
2021 UK Iron C11 0
2021 UK Iron C12 0
2021 UK Aluminium C1 0
2021 UK Aluminium C2 0
2021 UK Aluminium C3 0
2021 UK Aluminium C4 0
2021 UK Aluminium C5 0
2021 UK Aluminium C6 0
2021 UK Aluminium C7 0
2021 UK Aluminium C8 0
2021 UK Aluminium C9 0
2021 UK Aluminium C10 0
2021 UK Aluminium C11 0
2021 UK Aluminium C12 0
2021 USA Bronze C1 1
2021 USA Bronze C2 0
2021 USA Bronze C3 1
2021 USA Bronze C4 0
2021 USA Bronze C5 0
2021 USA Bronze C6 0
2021 USA Bronze C7 0
2021 USA Bronze C8 0
2021 USA Bronze C9 0
2021 USA Bronze C10 0
2021 USA Bronze C11 0
2021 USA Bronze C12 0
Hi Diptarup
Sorry I don't know how to modify the Rank measure...😅 But I found a workaround to get the same result. I summarize data into a new table with DAX, then add a rank column to this table. Just like below.
Create a new table:
RankTable = ADDCOLUMNS(SUMMARIZE('Table','Table'[Year],'Table'[Country],'Table'[Item]),"Average_of_Group",[Average of Group])Create a new column:
Index = RANKX('RankTable',[Average_of_Group],,DESC,Dense)At last, use columns from this RankTable to create a table visual.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
