Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Wait, 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
OK but I don't see a column with Q1, Q2, Q3, etc.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |