Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
If you are looking for Mode, see this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mode-Single-Column/m-p/359150#M101
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
)
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
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
That did the trick!
Thanks a lot:)