How "Strong" is a Median

[Sweet-T]

Hi everyone, 

I have a need to learn how "strong" a median is, meaning, how close the median is to changing to a different number. 

Motivation: my company reports all of our sales prices to a governing body, and the median of our sale prices becomes our list price for the next year. I want to know how close our median price is to jumping to a higher number, or falling to a lower one. 

Example: In the list below, the median is 5. I would like to display that the median is 1 away from changing to 4.

1, 2, 2, 3, 4, 5, 5, 5, 5, 18, 20

Any ideas would be apprecaited, thanks!

[Sweet-T · Accepted solution]

Solved it!
Used the Rank.EQ DAX function to help me achieve this. 

First I calculated high and low bounds of where the median falls: 

StrengthLow = 
    RANK.EQ(
        MEDIAN(Charges[Allowed Best])   // find the median of the allowed best to use as the target number to check list for
        ,Charges[Allowed Best]          // specify which column to use as list
        ,ASC                            // start from bottom of the list to the first instance (this will be the low)
        )

This will output the rank/position of the first instance of the median in the list. 

StrengthHigh = 
    COUNTROWS(Charges)                  // count the total number of rows in the Charge table
    -                                   // subtract the position of the first instance of the median
    (RANK.EQ(                           // find the first instance of the median
        MEDIAN(Charges[Allowed Best])   // find the median of the allowed best to use as the target number to check list for
        ,Charges[Allowed Best]          // specify which column to use as list
        ,DESC                           // from from top of the list to the first instance (this will be the high)
        ) +1                            // since this includes the ranked number, we actually want to add one (n+1) to this calculation
    )

This will output the rank/position of the last instance of the median, before the list switches to a new number. 

I then just used a tachometer visual with these as the high and low bounds to show the stability/strength of the median. 

Bounds are the High and Low measures created above

**Note: if you know your median, you can just substitute that in for Median(Charges[Allowed Best]).. I have several different products I want to check this against so this allows flexibilility based on page filters. 

[v-xicai]

Hi @Sweet-T ,

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis?Do mask sensitive data before uploading.

Best Regards,

Amy