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, but also show the total for all values in the table, not just the Top 20 total (as is displayed using the Top N filter).

For example, I have a website with 100 products and I have a measure of the number of hits per product. I want to see a table with the Top 20 products by no. of hits but also see the total number of hits for the website at the bottom of the table. How can I achieve this? Please provide baby steps.

thanks!

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

5 Replies

  • 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!!!

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    One way to do this. Top 20 products would be shown by name. Rest would be grouped into a single row

     

    Go to Modelling Tab and select the NEW TABLE button

     

    Top20 and Others =
UNION (
    TOPN ( 20, TableName, TableName[No of hits], DESC ),
    ROW (
        "Products", "Bottom 80",
        "No of hits", CALCULATE (
            SUM ( 'TableName'[No of hits] ),
            TOPN ( 80, TableName, TableName[No of hits], ASC )
        )
    )
)

    .