Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Top N with Total for all values

Hi, I feel this is simple - I have searched but cannot find a clear answer. Apologies if this is rather simplistic but this is relatively new to me. I want to show the Top 20 products by some value...
Need Help
  • BILASolution's avatar
    BILASolution
    8 years ago

    Hi Anonymous

     

    Try this...

     

    1. This is my sample data.

     

     

     

    2. and this is the result after apply TopN (In my case Top 3)

     

     

     

    3. The trick for showing the total below the table is the next measure...

     

    Total Quantity = 
IF
(
     HASONEVALUE('Top'[Products]);
     SUM('Top'[Quantity]);
     CALCULATE(SUM('Top'[Quantity]);ALL('Top'[Products]))
)

     

    I hope this helps

     

    Regards

    BILASolution

BILASolution's avatar
BILASolution
Icon for Solution Specialist rankSolution Specialist
8 years ago

Hi Anonymous

 

Try this...

 

1. This is my sample data.

 

 

 

2. and this is the result after apply TopN (In my case Top 3)

 

 

 

3. The trick for showing the total below the table is the next measure...

 

Total Quantity = 
IF
(
     HASONEVALUE('Top'[Products]);
     SUM('Top'[Quantity]);
     CALCULATE(SUM('Top'[Quantity]);ALL('Top'[Products]))
)

 

I hope this helps

 

Regards

BILASolution

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks, worked like a charm! :) (although I did have to change your semi-colons ; to commas ,  )

  • OECvargoj's avatar
    OECvargoj
    Frequent Visitor
    8 years ago

    Any advise for applying this same concept to a pie chart? I would like to show top 5 products by total sales while displaying the % of total for all products. Currently when I display top 5, the % of total is redistributed to only take into consideration the top 5.

    • RajGine20's avatar
      RajGine20
      New Member
      4 years ago

      Hey, did you find a solution to this problem?

      THANKS!!!