Forum Discussion

Paddhof1984's avatar
Paddhof1984
Helper III
9 years ago

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 rel...
Need Help
Tips and Tricks
Paddhof1984's avatar
Paddhof1984
Helper III
9 years ago

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.

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
    Chihiro
    Solution Sage
    9 years ago

    v-huizhn-msft

     

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

  • Paddhof1984's avatar
    Paddhof1984
    Helper III
    9 years ago

    v-huizhn-msftChihiro

     

    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:

     

     

    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,