Forum Discussion
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...
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
Community Champion
Community ChampionWait, which column specifies the quarter?
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.