looking to get most common value for a field during summarize

[rhaining]

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!

[Greg_Deckler]

@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