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.
I have data similar to this:
Sale Id | Item Id | Color
===============
1 | Toy Car | Blue
2 | Stuffed Animal | Green
3 | Toy Car | Red
4 | Toy Car | Blue
5 | Stuffed Animal | Purple
I want to summarize the most common color for each item. In effect I'm looking for the "mode" of a set of string values. So the output would be:
Item Id | Most Common Color | MCC Count
============================
Toy Car | Blue | 2
Stuffed Animal | Purple | 1
(Note that there is a tie for Green or Purple for Stuffed Animal. I'm not picky. I want one of the colors that is tied for top.)
Perf. is a bit of a concern so I'm hoping to find a way to do this in a single summarize operation.
I can live without the most common color count column, if that's harder to generate.
Thanks in advance!
@rhaining Try:
MCC Count Measure =
VAR __Table = SUMMARIZE('Table', [Color]), "__Count", COUNTROWS('Table'))
VAR __Result = MAXX(__Table, [__Count])
RETURN
__Result
Most Common Color Measure =
VAR __Table = SUMMARIZE('Table', [Color]), "__Count", COUNTROWS('Table'))
VAR __Max = MAXX(__Table, [__Count])
VAR __Result = MAXX(FILTER(__Table, [__Count] = __Max), [Color])
RETURN
__Result
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |