Sub-total - Most COUNT value in the group

[ngct1112]

Hi All,

I am trying hard to find a way to achieve my desired result.

In this group, 

Supplier A: occur 4 times

Supplier C: occur 1 time

Supplier E: occur 1 time

I am hoping the Sub-total can show the most frequent supplier, which means "A"

Please refer to the Sample BI 

Since "Supplier 1" itself is a measure, I know it will be a little tricky. 

Appreciated if I could get any help from here.

For more information, what I am trying to do is to create a table to show the users which suppliers with the lowest cost across all suppliers for each QTY .

[v-angzheng-msft · Accepted solution]

Hi, @ngct1112 

1. To create a calculated table:

subT = 
var _t1=SUMMARIZE('Table',[Quantity From],"Su",[Supplier 1])
return _t1

2. To create 5 measures

__isP1 = ISFILTERED('Table'[Product Type])

__isP2 = ISFILTERED('Table'[Quantity From])

P1+P2 = [__isP1]+[__isP2]

__maxCountSu = 
var _t=MAXX(TOPN(1,SUMMARIZE('subT',[Su],"count",COUNTX('subT',[Su])),[count]),[Su])
return _t

__Supplier 1.1 = 
SWITCH(
    [P1+P2],
    1,[__maxCountSu],[Supplier 1])

Then the result would be like this:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngct1112]

@v-angzheng-msft 

though totally have no idea how it happens, it works fine in the model.

Great thanks for your brillant solution.

[amitchandak]

@ngct1112 , Try TOPN, I am assuming it should work at sub total level too

CALCULATE(max(Data[Supplier]),TOPN(1,allselected(Data[Supplier]),[Supplier cost],DESC),VALUES(Data[Supplier]))

[ngct1112]

Hi @amitchandak , I tried to apply this formual but not working. Attached as Sample BI 

Please be reminded that

1. "Unit Price_FX" itself is a meaure.

2. "Supplier 1" is referring to the lowest cost supplier, filtered by a "Rank = 1" measure

Rank by Supplier = 
RANKX(
   FILTER(
       ALLSELECTED('Table'[Group],'Table'[Supplier]),
       'Table'[Group]=MAX('Table'[Group])),
       [UnitPrice_FX],,ASC)

Appreciated if there is any solution.