Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |