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

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

.