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 ...
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.

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.

     

     

    • manojk_pbi's avatar
      manojk_pbi
      Helper V
      3 years ago
      Hi Rico Zhou, Thanks for quick reply. Do we have option to pick always top N irrespective of repeated value with out any condition ? When I select Top 2 or 3 the result remains same for Product1.
      • Anonymous's avatar
        Anonymous
        Not applicable
        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.