show the most frequent value with an criteria

[Hawjeen]

Hi all,

1'st post, so sorry if it is a repated question, but i haven't been able to find what im looking for.

i need to findt the most frequent used store that a salesperson has done sales in

store	Initials	quantity	Reg_store
s1000	TH	5	s1000
s2000	TH	3	s1000
s1000	TH	4	s1000
s3000	CB	5	s1000

My issue is i don't how to get Reg_store. This is a very simple table and i have a more complicated with many stores and initials - i need to find the store where most of the sales are done through.

I really hope that someone is able to help me with this, i'm sure it is quite simple.

Regards Hawjeen

[Greg_Deckler]

If you are looking for Mode, see this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mode-Single-Column/m-p/359150#M101

[Greg_Deckler]

Well, if you want that as a column, you could do this:

Column = 
  VAR __Table = 
      SUMMARIZE(
        ALL('Table'),
        [store],
        "__Quantity",SUM('Table'[Quantity])
      )
  VAR __Max = MAXX(__Table,[__Quantity])
RETURN
  MAXX(
    FILTER(
      __Table,
      [__Quantity] = __Max
  )
  

[Hawjeen]

Hi,

Thanks for your reply, much appriciated!

It kind of works, but not fully. instead of returning the store with highest quantities it simply return the store with higesht number.

i think what i need is that is start with filtering on initials, then summarize the quantities to find the max, and then return the store number, could you help me tweak it?

Regards 

[v-shex-msft · Accepted solution]

Hi @Hawjeen,

I modify Greg_Deckler's formula to add new conditions, you can try to use below formula if it meets for your requirement:

most frequent Store =
VAR summary =
    SUMMARIZE (
        FILTER ( 'Table', [initials] = EARLIER ( 'Table'[initials] ) ),
        [store],
        [initials],
        "Total", SUM ( 'Table'[Quantity] )
    )
VAR _max =
    MAXX ( summary, [Total] )
RETURN
    MAXX ( FILTER ( summary, [Total] = _max ), [store] )

Regards,

Xiaoxin Sheng

[Hawjeen]

That did the trick!

Thanks a lot:)