Forum Discussion
Solved
create a calculated column to segment selected data based on 20/80 percent split
Hi
Can anyone one assist with helping me understand how a can create a calculated column that segments the data selected into top 20% vs bottom 80% split of the selected data. Using the below example of 50 records - when ranked highest to lowest -flag the top 20 % (10 records) as "top20percent" and the remaining records as "bottom80Percent".
Thanks in advance.
| Reference | Days | Flag |
| 215640009 | 171 | Top20Percent |
| 204643040 | 163 | Top20Percent |
| 201134031 | 160 | Top20Percent |
| 205662022 | 156 | Top20Percent |
| 201661023 | 147 | Top20Percent |
| 205140074 | 147 | Top20Percent |
| 203205017 | 133 | Top20Percent |
| 201620002 | 122 | Top20Percent |
| 201695012 | 114 | Top20Percent |
| 201193038 | 107 | Top20Percent |
| 215611018 | 107 | Bottom80Percent |
| 201134008 | 100 | Bottom80Percent |
| 201699105 | 99 | Bottom80Percent |
| 205140065 | 99 | Bottom80Percent |
| 205140047 | 95 | Bottom80Percent |
| 201693013 | 94 | Bottom80Percent |
| 215611027 | 93 | Bottom80Percent |
| 205140049 | 89 | Bottom80Percent |
| 205635007 | 88 | Bottom80Percent |
| 205662023 | 87 | Bottom80Percent |
| 201100036 | 86 | Bottom80Percent |
| 202198110 | 85 | Bottom80Percent |
| 201134027 | 84 | Bottom80Percent |
| 201661009 | 80 | Bottom80Percent |
| 205140076 | 78 | Bottom80Percent |
| 201191047 | 77 | Bottom80Percent |
| 201663014 | 77 | Bottom80Percent |
| 201100034 | 76 | Bottom80Percent |
| 203205020 | 74 | Bottom80Percent |
| 201134002 | 69 | Bottom80Percent |
| 203198072 | 69 | Bottom80Percent |
| 205122027 | 63 | Bottom80Percent |
| 205140036 | 60 | Bottom80Percent |
| 201100053 | 59 | Bottom80Percent |
| 215653001 | 49 | Bottom80Percent |
| 215105003 | 46 | Bottom80Percent |
| 205662018 | 43 | Bottom80Percent |
| 201134016 | 37 | Bottom80Percent |
| 201134029 | 36 | Bottom80Percent |
| 201134033 | 35 | Bottom80Percent |
| 201629003 | 35 | Bottom80Percent |
| 214108018 | 35 | Bottom80Percent |
| 201699040 | 32 | Bottom80Percent |
| 203109010 | 32 | Bottom80Percent |
| 205140023 | 31 | Bottom80Percent |
| 203110005 | 29 | Bottom80Percent |
| 215690007 | 25 | Bottom80Percent |
| 201689027 | 21 | Bottom80Percent |
| 215611062 | 21 | Bottom80Percent |
| 215690012 | 14 | Bottom80Percent |
2 Replies
Try this calculated column
MyFlag_ = VAR myref = [Reference] VAR rowcount = COUNTROWS ( Table1 ) VAR Top20rows = SELECTCOLUMNS ( TOPN ( INT ( rowcount * .2 ), Table1, [Days], DESC, [Reference], ASC ), "Ref", [Reference] ) RETURN IF ( myref IN Top20rows, "Top20 Percent", "Bottom80Percent" )