cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paddhof1984 Helper III
Helper III

Top N

Hello,

 

I do have another question regarding the filter on visual level for top n filter. Unfortunately MS Power BI filters the Data correctly, but in case I want to show percentage figures in relation to the total turnover, Power BI only refers to the top n filtered figures and not to the total turnover including all turnover of products, which are not listed while using top n filter.

 

So the percentage is only shown in total for the top n figures, somehow Power BI doesn't considers all other turnover figures, although those figures should be considered. Anyone an Idea how I can handle this problem?

8 REPLIES 8
Chihiro Solution Sage
Solution Sage

Re: Top N

Hard to give specifics without knowing your columns etc.

 

However, you can probably use measure to calculate total regardless of filter applied using something like below.

=CALCULATE(SUM(Column),FILTER(ALL(TABLE),Condition))

Paddhof1984 Helper III
Helper III

Re: Top N

Hello @Chihiro,

 

I do have following columns: product group name, turnover (year). So in case I am creating a new table chart with product group name as row name and I am using the values shown in the columns. But, like I already said, if I only want to show top n turnovers by products for a specifig year, MS Power BI calculates the percentage per product only from top n products and not total products. Power BI keeps cutting of the products and their turnover, which don't appear in the top n.

Chihiro Solution Sage
Solution Sage

Re: Top N


@Paddhof1984 wrote:

Power BI keeps cutting of the products and their turnover, which don't appear in the top n.


Hence, use of FILTER(ALL()) (or ALL()). Basically it ignores all applied filter and will evaluate based on entire table. Exact construct of formula will depend on your data set.

 

https://msdn.microsoft.com/en-us/library/ee634802.aspx

Highlighted
Microsoft
Microsoft

Re: Top N

Hi @Chihiro,

For one thing, You can create measure rather use the report visual filter level. For example, you want to display per year's top 5 products and sales.


You can create a new table by clicking "New Table" under modeling on home page.

New table=TOPN(5, SUMMARIZE(Table, [Year], [Product name],“TotalSales”, SUMX(Table[sales])),Table[sales])


For another method, you can create a calcualted column using the following formula. Then add the new column as visual filter, you can select top n, you will get expected result.

totalsales=CALCULATE(SUM(Table[sales]),ALLEXCEPT(Table,Table[Year],Table[Product name]))


If this is not what you want, please share more details or sample table for further analysis.

Thanks,
Angelia

Chihiro Solution Sage
Solution Sage

Re: Top N

@v-huizhn-msft

 

Didn't think of creating another table rather than visual. That'd make thing much simpler for OP I believe.

Paddhof1984 Helper III
Helper III

Re: Top N

@v-huizhn-msft@Chihiro

 

the thing about all this is: I do have 3 different tables:

 

LAKENN (this one includes the country codes)

PG (this one contains the product group name)

RE2015 (this one includes the turnover for the year 2015)

 

 

Here's a screenshot regarding the relationships between the tables:

 

Top10-filter.JPG

 

What i exactly need is: I want to show the top 10 figures by turnover by country (in monetary figure and in percentage of the whole turnover sum) for the specific product group. In case I choose the Top-N Filter for the top 10 product groups filtered by RE2015 (the turnover for the year 2015) the turnover figure by group is changed to point only out to the whole sum of the top 10, not all figures including the ones, which aren't shown due to the filter. Also the percentage changes only based on the total of the top 10 and not on all figures, including the ones which are not shown while filtered out.

 

Does anyone has a clue how to solve this problem?

 

I think all your suggestions might not work, 

Microsoft
Microsoft

Re: Top N

Hi @Paddhof1984,

Do you mind share your .pbix file, so that we can test it using your sample table.

 

Best Regards,
Angelia

Paddhof1984 Helper III
Helper III

Re: Top N

Hello  @v-huizhn-msft

 

here's a link to another post from me where I posted a shared link for the pbix-file:

 

http://community.powerbi.com/t5/Desktop/YTD-Linechart-comparison-of-different-years/m-p/205589#M9054...

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors