Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
3 years ago
Solved

Top N in table with multiple columns

Hi Friends,

 

I am looking for a suggestion or solution on how to create a table with Top N Customers on sales with other details of customer in the table. Top N can be on entire data or it can be on slicer on ProductType

Appreciate any suggestions here. 

 

Below is the sample data

IDProductTypeTrendTitleStateResponsibleExposure
39428Product1StableTitle1ClosedUser120
162792Product1IncreasingTitle2NewUser216
173226Product1DecreasingTitle3ResolvedUser316
65706Product1StableTitle4ClosedUser416
86525Product2StableTitle5ClosedUser516
134931Product2StableTitle6ClosedUser615
27591Product2StableTitle7ClosedUser715
27593Product2DecreasingTitle8ClosedUser815
38499Product2DecreasingTitle9ClosedUser915
112655Product2StableTitle10ClosedUser1012
129003Product1StableTitle11MitigatedUser1112
135077Product1DecreasingTitle12MitigatedUser1212
138692Product1StableTitle13ClosedUser1312
139250Product1StableTitle14ClosedUser1412
181593Product1StableTitle15NewUser1512
27707Product3IncreasingTitle16ClosedUser1612
29673Product3DecreasingTitle17ClosedUser1712
42179Product3StableTitle18ClosedUser1812
49670Product3StableTitle19ClosedUser1912
59744Product3StableTitle20MitigatedUser2012
61017Product1StableTitle21ClosedUser2112
61018Product1StableTitle22ClosedUser2212
69808Product2DecreasingTitle23ClosedUser2312
162795Product2IncreasingTitle24NewUser2410
27575Product2StableTitle25MitigatedUser2510
36986Product2StableTitle26ClosedUser2610
40810Product3StableTitle27ActiveUser2710
105224Product3DecreasingTitle28MitigatedUser289
132406Product1StableTitle29ActiveUser299
27595Product3StableTitle30MitigatedUser309
40809Product1StableTitle31MitigatedUser319
100761Product1StableTitle32ClosedUser326
112651Product2DecreasingTitle33ClosedUser336
114514Product2StableTitle34MitigatedUser346
129010Product2StableTitle35MitigatedUser356
135246Product1StableTitle36ClosedUser366
139228Product2StableTitle37NewUser376
27569Product2StableTitle38ClosedUser386
27572Product2DecreasingTitle39ClosedUser396
27574Product1StableTitle40ClosedUser406
27580Product2StableTitle41MitigatedUser416
27588Product2StableTitle42ClosedUser426
27594Product2StableTitle43ClosedUser436
27599Product1StableTitle44ClosedUser446
33949Product2DecreasingTitle45ActiveUser456
38486Product2StableTitle46ActiveUser466
38491Product2DecreasingTitle47ClosedUser476
61670Product3StableTitle48ClosedUser485
61671Product1StableTitle49ClosedUser495
86524Product1StableTitle50ClosedUser505

 

Thanks,

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi manojk_pbi ,

     

    As above I add a rank in my calculation, so if you want to show TOP N in another way, we need to add some condtions in my code to create new rank.

    You can show me a screenshot with the result you want as Product Top2 and Top3, I will try to find the condtion to help you solve your issue.

     

    Best Regards,
    Rico Zhou

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi manojk_pbi ,

     

    I suggest you to create a number range slicer with what is parameter function.

    Then create a DimProductType table for slicer.

    Relationship:

    Measure:

    Rank =
VAR _SELECTTOPN =
    SELECTEDVALUE ( 'Top N'[Top N] )
VAR _RANK =
    IF (
        ISFILTERED ( DimProduct[ProductType] ),
        RANKX (
            ALLEXCEPT ( 'Table', 'Table'[ProductType] ),
            CALCULATE ( SUM ( 'Table'[Exposure] ) )
        ),
        RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Exposure] ) ),, DESC, DENSE )
    )
RETURN
    IF ( ISFILTERED ( 'Top N'[Top N] ), IF ( _RANK <= _SELECTTOPN, 1, 0 ), 1 )

    Add this measure into visual level filter and set it to show items when value = 1.

     

    Best Regards,
    Rico Zhou

     

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

     

    • manojk_pbi's avatar
      manojk_pbi
      Helper V
      3 years ago

      Thanks v-rzhou-msft, the solution is close to my requirement. One thing i noticed is eventhough we select Top 2 i could see 4 records with Product1 selected. Why is so, how should we restrict to show only 2 ?

      Thanks

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi manojk_pbi ,

         

        The second value is 16, however there are three data as 16 in Product 1, all of them will be marked as second one.

        If you want to sort these three data with same value 16, you need a new condtion like title sort or others. Please tell me more details.

         

        Best Regards,
        Rico Zhou

         

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