Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
  • smpa01's avatar
    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
                )
            )
        )