Top N nested

[Anonymous]

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

[v-alq-msft]

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

[v-alq-msft · Accepted solution]

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]

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