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 d...
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

     

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