How to count the MAX number of Group and find which group

[ngct1112]

Hi,

I am trying to figure out how to find the Highest count of each group. Simplified data as below

From this table, we can find that there are 

7 items in Group A

3 items in Group B

9 items in Group C

Items	Group
1	A
2	A
3	A
4	C
5	C
6	A
7	C
8	C
9	A
10	A
11	C
12	A
13	C
14	C
15	B
16	B
17	C
18	B
19	C

Is it possible I could write 2 measures to find out 

1.) Most items' Group: C

2.) Highest Items: 9

Appreciate your help!

[amitchandak · Accepted solution]

@ngct1112 , Create two measures like

Top 1 count = CALCULATE(calculate(count(Table[Group])),TOPN(1,all(Table[Group]),calculate(count(Table[Group])),DESC),VALUES(Table[Group]))

Top 1 Group = CALCULATE(calculate(max(Table[Group])),TOPN(1,all(Table[Group]),calculate(count(Table[Group])),DESC),VALUES(Table[Group]))

[CNENFRNL · Accepted solution]

Hi, @ngct1112 , you might want to use the following measures,

Group = 
VAR __sum =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, Table1[Group] ),
        "Count", CALCULATE ( COUNTROWS ( Table1 ) )
    )
RETURN
    MAXX ( TOPN ( 1, __sum, [Count] ), Table1[Group] )

Most Items = 
VAR __sum =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, Table1[Group] ),
        "Count", CALCULATE ( COUNTROWS ( Table1 ) )
    )
RETURN
    MAXX ( __sum, [Count] )

Btw, Power Query easily does the trick,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc2xEYAgFIPhXVJTGFGEUh3jHfuvIchd0n33p0gEiIQbPQV2KUvH0PvrlIrWS61KTSs3k9rpG2ZXH3E+PYvF9Bera1u1fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Items = _t, Group = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Group"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] = List.Max(#"Grouped Rows"[Count]))
in
    #"Filtered Rows"

[amitchandak · Accepted solution]

@ngct1112 , Create two measures like

Top 1 count = CALCULATE(calculate(count(Table[Group])),TOPN(1,all(Table[Group]),calculate(count(Table[Group])),DESC),VALUES(Table[Group]))

Top 1 Group = CALCULATE(calculate(max(Table[Group])),TOPN(1,all(Table[Group]),calculate(count(Table[Group])),DESC),VALUES(Table[Group]))