cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClaytonMUK Frequent Visitor
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
BILASolution Established Member
Established Member

Re: Top N with Total for all values

Hi @ClaytonMUK

 

Try this...

 

1. This is my sample data.

 

data.png

 

 

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

 

resultado.png

 

 

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

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Top N with Total for all values

Hi @ClaytonMUK

 

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

Try my new Power BI game Cross the River
BILASolution Established Member
Established Member

Re: Top N with Total for all values

Hi @ClaytonMUK

 

Try this...

 

1. This is my sample data.

 

data.png

 

 

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

 

resultado.png

 

 

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

View solution in original post

ClaytonMUK Frequent Visitor
Frequent Visitor

Re: Top N with Total for all values

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

Highlighted
OECvargoj Frequent Visitor
Frequent Visitor

Re: Top N with Total for all values

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.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors