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

Need Help
Tips and Tricks

8 Replies

  • Chihiro's avatar
    Chihiro
    Solution Sage
    9 years ago

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