Forum Discussion

zsripowerbi's avatar
zsripowerbi
Regular Visitor
2 years ago
Solved

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. 

 

  • lbendlin's avatar
    lbendlin
    2 years ago

    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

    • zsripowerbi's avatar
      zsripowerbi
      Regular 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's avatar
        lbendlin
        Icon for Super User rankSuper 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.