Calculating Median, Q1, Q3 dynamically

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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

[Greg_Deckler]

Wait, which column specifies the quarter?

[Anonymous]

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

[Greg_Deckler]

OK but I don't see a column with Q1, Q2, Q3, etc.