Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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.

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

@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, 

Hi @Paddhof1984,

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

 

Best Regards,
Angelia

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

@v-huizhn-msft

 

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


@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.