Forum Discussion
TopN + Others dynamic
Hello Everyone,
I have a requirement where i need to show TOP N + Others dynamically for different tables. There are many tables so i want to make it dynamic. below is the code that works correctly for one table but when i try to make it dynamic, where the table is selected via slicer it does not work.
working code for one table
DEFINE
MEASURE 'TopSalesTerritory'[Top N Sales] =
VAR TopNValue = ParamTopN[ParamTopN Value]
var ttable = ALLSELECTED(TopSalesTerritory)
var ttablecolumn = SELECTEDVALUE(TopSalesTerritory[Country])
--table with just top n ordered by sales amount
VAR TopNSalesTerritory =
TOPN(
TopNValue,
ttable,
MeasureTable[SumSalesAmount]
)
-- returns total for sales amount for all sales territory(not just top n)
VAR AllSales =
CALCULATE(
MeasureTable[SumSalesAmount],
ttable
)
-- total sales for "other" than the ones included in top N
-- other sales = all sales - sum(top n sales)
-- retuns sum of other sales
VAR OtherSales = AllSales - CALCULATE(MeasureTable[SumSalesAmount], TopNSalesTerritory)
-- returns sum for only Top N Sales Territory
var TopNSales =
CALCULATE(
MeasureTable[SumSalesAmount],
KEEPFILTERS(
TopNSalesTerritory
)
)
var CurrentSalesTerritory = ttablecolumn
RETURN
IF(
ISBLANK(CurrentSalesTerritory),
TopNSales + OtherSales,
IF(
CurrentSalesTerritory = "Other",
OtherSales,
TopNSales
)
)
EVALUATE
SUMMARIZECOLUMNS(
"Top N Sales", 'TopSalesTerritory'[Top N Sales]
)
I have tried the below code to make it dynamic but it does not work and TOPN function does not accept it. eventually, i want to have switch so i can change tables based on slicer selection.
VAR tablename = "TopSalesTerritory" //coming from slicer
VAR ttable =
IF (
tablename = "TopSalesTerritory",
ALLSELECTED(TopSalesTerritory),
ALLSELECTED(TopQuantityTerritory)
)
VAR TopNSalesTerritory =
TOPN(
TopNValue,
ttable,
MeasureTable[SumSalesAmount]
)
i have tried the same for measure and it does not work either.
VAR selectedMeasure = "SumSalesAmount" //coming from slicer
VAR tmeasure=
IF (
selectedMeasure = "SumSalesAmount",
[SumSalesAmount],
[SumQuantityAmount]
)
VAR TopNSalesTerritory =
TOPN(
TopNValue,
ttable,
tmeasure
)
Please let me know what i am missing. thank you for your time.
Below is the desired result, where i am creating multiple measures for each top N for single table and then have to repeat the same for other tables (sales type) as needed.
You're not missing anything. If you read the article carefully you will appreciate how insanely complex the solution is for a "simple" scenario with one column and a fixed TOPN(5). You are pushing that concept way, way, way beyond what is currently possible.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41
Note: There are very simple solutions for "Top x percent and other". Maybe that is an alternative.
3 Replies
- lbendlin
Super User
I would recommend you read that article and then rethink your requirements.
Filtering the top products alongside the other products in Power BI - SQLBI
- zsripowerbiRegular Visitor
Hello lbendlin , thanks for looking into this. the article only shows ONE column sorted as TOPN with others, my requirement is to show - Product, TOPNSalePrice, TOPNUnitPrice. with the introduction of mutliple columns around product i am creating 2 measures per table. so i have requirement of multiple tables like below. with this i am having to create multiple measures per table and wanted to make it dynamic. i dont see this option in the article provided unless i am missing it 😞
SaleType, TOPNSalePrice, TOPNUnitPrice
Country, TOPNSalePrice, TOPNUnitPrice
OrderType, TOPNSalePrice, TOPNUnitPrice- lbendlin
Super User
You're not missing anything. If you read the article carefully you will appreciate how insanely complex the solution is for a "simple" scenario with one column and a fixed TOPN(5). You are pushing that concept way, way, way beyond what is currently possible.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41
Note: There are very simple solutions for "Top x percent and other". Maybe that is an alternative.