cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Radkos Regular Visitor
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:

 

30112018 Q1Q3.PNG

 

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

11.png

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]))

 

 

9.png

 

10.png

 

Best Regards

Maggie

 

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


Radkos Regular Visitor
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
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
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

11.png

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]))

 

 

9.png

 

10.png

 

Best Regards

Maggie