Calculating the mode of the values of multiple measures

[ShNBl84]

I am trying to find the mode brackets of items being sold at different locations.

I have four measures that can each equal the values of 0, 1000, 5000, 10,000, 20,000, 30,000, or 40,000.

(4 measures with 7 possible results)

I have seen several responses on how to calculate the mode of a column or one measure, but have not found one that uses multiple measures.

I have them in measures because they are calculated results being displayed in a matrix.

The mode for Item A should be 1,000 (biased toward the higher bracket) and Item B should be 0.

Any help would be much appreciated!

Thanks!

[jdbuchanan71 · Accepted solution]

I think I got it to work.
First find any matching pairs then find any second matching pairs.  Since you only have 4 items the only thing that would beat a matched pair is a higher matched pair.  If you have any matched pair that will beat any single so I find the highest pair and if there is no pair find the highest value by comapring 2 then feeding that to the next compare and getting the max again.

Mode Measure = 
VAR N = [North]
VAR S = [South]
VAR E = [East]
VAR W = [West]

VAR FirstDouble = IF ( N = S || N = E || N = W, N )
VAR SecondDouble = IF ( S = E || S = W, S )
VAR ThirdDouble = IF ( E = W, E )
VAR MaxDouble = MAX ( MAX ( FirstDouble, SecondDouble ), ThirdDouble )

VAR FirstSingle = MAX ( N, S )
VAR SecondSingle = MAX ( FirstSingle, E )
VAR ThirdSingle = MAX ( SecondSingle, W )

VAR Result = IF( ISBLANK ( MaxDouble ), ThirdSingle, MaxDouble)

RETURN Result

*Fixed one error and change the original measures to pull to variables so they don't have to calc multiple times.

[ShNBl84]

Artfully done sir. Thank you!

[Cmcmahan]

Should be doable if you follow this post and use a mode calculation instead of a median: https://community.powerbi.com/t5/Desktop/How-to-calculate-a-median-across-measures/td-p/154949