Forum Discussion
Top Quartile with All statement
Hi All,
I am trying to report the Top Quartile over a measure.
The first 3 columns of the table is the data to work with and the final column is the desired outcome.
| School | Responses | Measure | Top_Quartile This column is the desired outcome |
| Record 1 | 1048 | 87.98 | 91.96 |
| Record 2 | 1082 | 91.68 | 91.96 |
| Record 3 | 1342 | 92.25 | 91.96 |
| Record 4 | 252 | 90.48 | 91.96 |
| Record 5 | 479 | 88.94 | 91.96 |
| Record 6 | 1875 | 92.05 | 91.96 |
| Record 7 | 341 | 92.96 | 91.96 |
| Record 8 | 1629 | 89.07 | 91.96 |
| Record 9 | 198 | 81.82 | 91.96 |
| Record 10 | 202 | 89.60 | 91.96 |
| Record 11 | 753 | 84.86 | 91.96 |
| Record 12 | 133 | 97.74 | 91.96 |
| Record 13 | 2229 | 88.25 | 91.96 |
| Record 14 | 1292 | 85.06 | 91.96 |
| Record 15 | 143 | 81.82 | 91.96 |
| Record 16 | 752 | 85.90 | 91.96 |
| Record 17 | 141 | 64.54 | 91.96 |
| Record 18 | 1141 | 93.08 | 91.96 |
| Record 19 | 1108 | 86.37 | 91.96 |
| Record 20 | 961 | 87.72 | 91.96 |
I need to be able to filter out all records with fewer than 100 responses for the real data, both responses and Measure are measures.
I am currently using the formula below but I cant seem to wrap the all statement correctly, I have tried variations of this but the latest failed formula is:
Top_Quartile_Sch3 =
VAR Summary =
ADDCOLUMNS(
SUMMARIZE(
'_Source Data_Course',
'_Source Data_Course'[School]
),
"Measure",IF([Responses]>99,[Measure])
)
VAR TQ = CALCULATE(PERCENTILEX.INC(Summary,
[Measure],0.75
),ALL('_Source Data_Course'[School]))
RETURN
TQ
Help would be greatly appreciated 🙂
2 Replies
- v-eachen-msft
Community Support
Hi Anonymous ,
You could try the following DAX:
Top_Quartile_Sch3 = VAR Summary = ADDCOLUMNS ( SUMMARIZE ( ALL ( '_Source Data_Course' ), '_Source Data_Course'[School] ), "Measure", IF ( [Responses] > 99, [Measure] ) ) VAR TQ = PERCENTILEX.INC ( Summary, [Measure], 0.75 ) RETURN TQHi v-eachen-msft, thanks for this, its not returning any results unfortunately.
