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.
I am currently using this formula to calculate a value,
Solved! Go to Solution.
I hate to be the bearer of bad news, but I'm assuming from the syntax that this is a calculated column. Therefore the PERCENTILE.INC function is calculating over the entire column, not just for 18-29 females in Phase 1. If you want to calculate over a limited population in a calculated column you should probably use PERCENTILEX.INC over a filtered set of data.
You could do something like the following to capture the phase of the current row and then calculate the percentile within that population. (note I'm also using variables to avoid repeatedly calculating the percentile values)
eg.
Reslnc_F_18 - 29 = VAR _currentRowPhase = 'D Star Survey'[phases] VAR _25Percentile = PERCENTILEX.INC ( FILTER ( 'D Star Survey', 'D Star Survey'[phases] = _currentRowPhase && 'D Star Survey'[Age_group] = "18 - 29" && 'D Star Survey'[Gender] = "Female" ), 'D Star Survey'[Survey Score - Copy], 0.25 ) VAR _75Percentile = PERCENTILEX.INC ( FILTER ( 'D Star Survey', 'D Star Survey'[phases] = _currentRowPhase && 'D Star Survey'[Age_group] = "18 - 29" && 'D Star Survey'[Gender] = "Female" ), 'D Star Survey'[Survey Score - Copy], 0.75 ) RETURN IF ( 'D Star Survey'[Age_group] = "18 - 29", IF ( 'D Star Survey'[Gender] = "Female", IF ( MAX ( 'D Star Survey'[Observation Date] ), IF ( 'D Star Survey'[Survey Score - Copy] > _25percentile && 'D Star Survey'[Survey Score - Copy] < _75percentile, "Moderate", IF ( 'D Star Survey'[Survey Score - Copy] <= _25percentile, "Low", IF ( 'D Star Survey'[Survey Score - Copy] >= _75percentile, "High", "" ) ) ) ) ) )
I hate to be the bearer of bad news, but I'm assuming from the syntax that this is a calculated column. Therefore the PERCENTILE.INC function is calculating over the entire column, not just for 18-29 females in Phase 1. If you want to calculate over a limited population in a calculated column you should probably use PERCENTILEX.INC over a filtered set of data.
You could do something like the following to capture the phase of the current row and then calculate the percentile within that population. (note I'm also using variables to avoid repeatedly calculating the percentile values)
eg.
Reslnc_F_18 - 29 = VAR _currentRowPhase = 'D Star Survey'[phases] VAR _25Percentile = PERCENTILEX.INC ( FILTER ( 'D Star Survey', 'D Star Survey'[phases] = _currentRowPhase && 'D Star Survey'[Age_group] = "18 - 29" && 'D Star Survey'[Gender] = "Female" ), 'D Star Survey'[Survey Score - Copy], 0.25 ) VAR _75Percentile = PERCENTILEX.INC ( FILTER ( 'D Star Survey', 'D Star Survey'[phases] = _currentRowPhase && 'D Star Survey'[Age_group] = "18 - 29" && 'D Star Survey'[Gender] = "Female" ), 'D Star Survey'[Survey Score - Copy], 0.75 ) RETURN IF ( 'D Star Survey'[Age_group] = "18 - 29", IF ( 'D Star Survey'[Gender] = "Female", IF ( MAX ( 'D Star Survey'[Observation Date] ), IF ( 'D Star Survey'[Survey Score - Copy] > _25percentile && 'D Star Survey'[Survey Score - Copy] < _75percentile, "Moderate", IF ( 'D Star Survey'[Survey Score - Copy] <= _25percentile, "Low", IF ( 'D Star Survey'[Survey Score - Copy] >= _75percentile, "High", "" ) ) ) ) ) )
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |