Forum Discussion
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))
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,
AngeliaDidn'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.
