Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Top N nested

Hello everyone,

 

I am strugling to make a nested Top N.

 

The data I am currently working with is in a graph which looks like:

 

Product                             Divison                      SOH

A1711                               Car                               1259

A254                                  Van                              998

A2222                              Car                               18

A447                                  Van                              800

b2222                               Car                              13000

p888                                  Truck                         65413

k874                                  Van                             82

w841                                 Truck                        123

 

What I need is this to be ranked by SOH based by divison. Looking like the below

 

Product                             Divison                      SOH                          Rank

b2222                                Car                              13000                           1

A1711                               Car                               1259                             2

A2222                               Car                               18                                   3

A254                                  Van                              998                                1

A447                                  Van                              800                                2

k 874                                 Van                               82                                  3

p888                                  Truck                          65413                          1

w841                                 Truck                         123                                 2

 

Ideally I would need to be able to set the amount being shown within the graph and able to change on demand.

 

I am quite new to Power BI, so sorry if I am asking something simple.

 

Regards,

 

L

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, there are three ways o achieve your requirement.

     

    Way1:

    You may create a calculated column as follows.

     

     

    RankColumn = 
COUNTROWS(
         FILTER(
             'Table',
             'Table'[Division] = EARLIER('Table'[Division])&&
             'Table'[SOH] > EARLIER('Table'[SOH])
         )
)+1

     

     

     

    Result:

     

    Way2:

    You may create a measure as below.

     

     

    RankMeasure = 
RANKX(
    ALLSELECTED('Table'[Product]),
    CALCULATE(SUM('Table'[SOH])),
    ,
    DESC,
    Dense
)

     

     

     

    Result:

     

    Way3:

    You may create a calculated table as follows.

     

     

    Table 3 = 
SUMMARIZE(
    'Table',
    'Table'[Product],
    'Table'[Division],
    'Table'[SOH],
    "rank",
    var _currentdivision = MAX('Table'[Division])
    var _currentsoh = MAX('Table'[SOH])
    return 
    COUNTROWS(
             FILTER(
                 ALL('Table'),
                 'Table'[Division] = _currentdivision&&
                 'Table'[SOH] > _currentsoh
             )
    )+1
)

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, there are three ways o achieve your requirement.

     

    Way1:

    You may create a calculated column as follows.

     

     

    RankColumn = 
COUNTROWS(
         FILTER(
             'Table',
             'Table'[Division] = EARLIER('Table'[Division])&&
             'Table'[SOH] > EARLIER('Table'[SOH])
         )
)+1

     

     

     

    Result:

     

    Way2:

    You may create a measure as below.

     

     

    RankMeasure = 
RANKX(
    ALLSELECTED('Table'[Product]),
    CALCULATE(SUM('Table'[SOH])),
    ,
    DESC,
    Dense
)

     

     

     

    Result:

     

    Way3:

    You may create a calculated table as follows.

     

     

    Table 3 = 
SUMMARIZE(
    'Table',
    'Table'[Product],
    'Table'[Division],
    'Table'[SOH],
    "rank",
    var _currentdivision = MAX('Table'[Division])
    var _currentsoh = MAX('Table'[SOH])
    return 
    COUNTROWS(
             FILTER(
                 ALL('Table'),
                 'Table'[Division] = _currentdivision&&
                 'Table'[SOH] > _currentsoh
             )
    )+1
)

     

     

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Please this article

    https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

     

    RANKX(all('Item'[Product]),[SOH],,DESC,Dense) 
RANKX(all('Item'[Product]),calculate(sum([SOH])),,DESC,Dense)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan