Forum Discussion
Anonymous
4 years agoNot applicable
top 3 columns for each zip code
Hello, My data is as follows: ID NAME IND BANGL BHUTN BURMS CAMBD CHINS 47616 Griffin 0 0 0 0 0 0 47620 Mount Vernon 11 0 0 0 0 15 47631 New Harmony 0 0 0 0 ...
- 4 years ago
Anonymous for a stater you need to unpivot the table. Also the data provided here is not suitable for TOPN filtering cause the top 3 values are 0,1,>1 for any given ID. In this TOPN(3) would return all the values
But let's suppose you had a table like following and you had it unpivoted
| ID | NAME | IND | BANGL | BHUTN | BURMS | CAMBD | CHINS | |-------|--------------|-----|-------|-------|-------|-------|-------| | 47616 | Griffin | 102 | 290 | 358 | 360 | 186 | 310 | | 47620 | Mount Vernon | 339 | 399 | 274 | 305 | 199 | 124 | | 47631 | New Harmony | 161 | 276 | 283 | 170 | 336 | 284 | | 47633 | Poseyville | 237 | 370 | 300 | 242 | 399 | 292 | | 47638 | Wadesville | 355 | 188 | 272 | 259 | 375 | 142 | | 47665 | Owensville | 214 | 269 | 335 | 240 | 282 | 326 | | 46164 | Nineveh | 171 | 158 | 335 | 329 | 218 | 376 | | 47001 | Aurora | 207 | 393 | 223 | 256 | 311 | 212 | | 47006 | Batesville | 378 | 364 | 327 | 197 | 152 | 184 | | 47011 | Bennington | 200 | 298 | 153 | 116 | 307 | 158 | | 47017 | Cross Plains | 151 | 175 | 300 | 266 | 365 | 292 | | 47018 | Dillsboro | 270 | 376 | 310 | 281 | 310 | 311 | | 47020 | Florence | 361 | 251 | 388 | 225 | 364 | 108 | | 47021 | Friendship | 229 | 305 | 261 | 191 | 328 | 163 | | 47022 | Guilford | 397 | 151 | 331 | 200 | 317 | 312 |you can write following two measures to give you what you need
_sum = SUM('Table 1'[Value]) Measure = VAR _id = MAX ( 'Table 1'[ID] ) RETURN CALCULATE ( [_sum], KEEPFILTERS ( TOPN ( 3, FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[ID] = _id ), [_sum], DESC ) ) )
smpa01
4 years agoCommunity Champion
Anonymous for a stater you need to unpivot the table. Also the data provided here is not suitable for TOPN filtering cause the top 3 values are 0,1,>1 for any given ID. In this TOPN(3) would return all the values
But let's suppose you had a table like following and you had it unpivoted
| ID | NAME | IND | BANGL | BHUTN | BURMS | CAMBD | CHINS |
|-------|--------------|-----|-------|-------|-------|-------|-------|
| 47616 | Griffin | 102 | 290 | 358 | 360 | 186 | 310 |
| 47620 | Mount Vernon | 339 | 399 | 274 | 305 | 199 | 124 |
| 47631 | New Harmony | 161 | 276 | 283 | 170 | 336 | 284 |
| 47633 | Poseyville | 237 | 370 | 300 | 242 | 399 | 292 |
| 47638 | Wadesville | 355 | 188 | 272 | 259 | 375 | 142 |
| 47665 | Owensville | 214 | 269 | 335 | 240 | 282 | 326 |
| 46164 | Nineveh | 171 | 158 | 335 | 329 | 218 | 376 |
| 47001 | Aurora | 207 | 393 | 223 | 256 | 311 | 212 |
| 47006 | Batesville | 378 | 364 | 327 | 197 | 152 | 184 |
| 47011 | Bennington | 200 | 298 | 153 | 116 | 307 | 158 |
| 47017 | Cross Plains | 151 | 175 | 300 | 266 | 365 | 292 |
| 47018 | Dillsboro | 270 | 376 | 310 | 281 | 310 | 311 |
| 47020 | Florence | 361 | 251 | 388 | 225 | 364 | 108 |
| 47021 | Friendship | 229 | 305 | 261 | 191 | 328 | 163 |
| 47022 | Guilford | 397 | 151 | 331 | 200 | 317 | 312 |
you can write following two measures to give you what you need
_sum = SUM('Table 1'[Value])
Measure =
VAR _id =
MAX ( 'Table 1'[ID] )
RETURN
CALCULATE (
[_sum],
KEEPFILTERS (
TOPN (
3,
FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[ID] = _id ),
[_sum], DESC
)
)
)