cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sweet-T Member
Member

How "Strong" is a Median

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sweet-T Member
Member

Re: How "Strong" is a Median

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. 

StrengthofMedian.PNGBounds 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. 

 

2 REPLIES 2
v-xicai New Contributor
New Contributor

Re: How "Strong" is a Median

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

Highlighted
Sweet-T Member
Member

Re: How "Strong" is a Median

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. 

StrengthofMedian.PNGBounds 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.