cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Regular Visitor

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculating Median, Q1, Q3 dynamically

Hi @Radkos

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 4
Super User

## Re: Calculating Median, Q1, Q3 dynamically

Wait, which column specifies the quarter?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Calculating Median, Q1, Q3 dynamically

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

Super User

## Re: Calculating Median, Q1, Q3 dynamically

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team

## Re: Calculating Median, Q1, Q3 dynamically

Hi @Radkos

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