GroupBy function

[Diptarup]

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.

[v-jingzhang]

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.

[Diptarup]

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

[v-jingzhang · Accepted solution]

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.

[Diptarup]

Many thanks. Really Appreciate your help. 

[amitchandak]

@Diptarup , You need to create a new column

Switch(True(),

[Category] <=4, "Group 1",

[Category] <=8, "Group 2",

 "Group 3"

)

[Diptarup]

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.