Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating Median, Q1, Q3 dynamically

Hi,

Another problem that is giving me a headache is calculating Q1, Q3 of age, when i have also second column with information about how many pieces left with that age, also i want this data to be dinamic acording to other columns like country etc. Below is simplified sample of my data:

 

 

So now Sum of Value Column is 136, Q1 value is 34, So Q1Age is 1 and Q3 value is 102, so Q3Age is 10, Median is for Q2 68, AgeQ2 is 2.

 

And when i will filter Only group 1 Sum of Value is 53, AgeQ1 is 1 AgeQ3 is also 1.

 

With formula should i use to calculate this? Sumx with some filters and allexpects? maybe earlier function?

 

Thank you for every responce

Radkos

 

 

Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    If i understand you correctly, 

    In statistics, Median, Q1, Q3 are referred to 50%,25%,75%.

    Create a Index column in Queries Editor

    Create measures

    Q1_value = (0.25)*SUMX(ALLSELECTED(Sheet3),[Value])

M_value = (0.5)*SUMX(ALLSELECTED(Sheet3),[Value])

Q3_value = (0.75)*SUMX(ALLSELECTED(Sheet3),[Value])

Q1_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[Q1_value]&&[sum cl]<[M_value]))

M_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[M_value]&&[sum cl]<[Q3_value]))

Q3_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[Q3_value]))

     

     

     

     

    Best Regards

    Maggie

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Column with age is our statistic value, bue column value is how many pieces with this group/country/age were delivered.

      That means, when we have Age 1 Value 25 its like 25 rows of the data with age 1

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous

    If i understand you correctly, 

    In statistics, Median, Q1, Q3 are referred to 50%,25%,75%.

    Create a Index column in Queries Editor

    Create measures

    Q1_value = (0.25)*SUMX(ALLSELECTED(Sheet3),[Value])

M_value = (0.5)*SUMX(ALLSELECTED(Sheet3),[Value])

Q3_value = (0.75)*SUMX(ALLSELECTED(Sheet3),[Value])

Q1_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[Q1_value]&&[sum cl]<[M_value]))

M_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[M_value]&&[sum cl]<[Q3_value]))

Q3_Age = CALCULATE(MIN([Age]),FILTER(ALLSELECTED(Sheet3),[sum cl]>=[Q3_value]))

     

     

     

     

    Best Regards

    Maggie