Forum Discussion

PBI12345's avatar
PBI12345
Icon for Helper I rankHelper I
1 year ago
Solved

Dynamic Top N calculation for time series visual

Hello,

 

I have created a dynamic Top N calculation which follows the DAX logic in an article linked here. I have also pasted this DAX at the bottom this message.

 

Whilst this measure works as intended, when used in a time series line graph it evaluates the top N selection at each data point. Instead, I want it to evaluate across the entire data range in the visualisation - the same way the Top N filter works when applied to a graph.

 

Please see example below:

 

Would be extremely grateful for assistance on this one -- have sunk considerable time in without any result! 

 

DAX from linked article above:

 

Top Orders | Cities = 

VAR N = 'Top N'[Top N Value]

-- Get the top N cities by total orders
VAR Top_Cities = 
    TOPN( 
        N, 
        ALLSELECTED('Cities'[City]), 
        [Total Orders] 
    )

-- Calculate the total orders for the Top N cities
VAR TopN_Orders = 
    CALCULATE(
        [Total Orders],
        KEEPFILTERS(Top_Cities)
    )

-- Calculate total orders for all selected cities
VAR All_Orders = 
    CALCULATE(
        [Total Orders],
        ALLSELECTED(Cities)
    )

-- Calculate the total orders for cities that are not in the Top N
VAR Other_Orders = 
    All_Orders - CALCULATE([Total Orders], Top_Cities)


VAR Result = 
    SWITCH(
        TRUE(),
        NOT ISINSCOPE(Cities[City]), All_Orders,
        SELECTEDVALUE(Cities[City]) = "Others", Other_Orders,
        TopN_Orders
    )



RETURN 
    Result

 

 

 

 

  • I think you need to tweak the Top_Cities variable to use ALLSELECTED('Date')

    Top Orders | Cities =
    VAR N = 'Top N'[Top N Value] -- Get the top N cities by total orders
    VAR Top_Cities =
        CALCULATETABLE (
            TOPN ( N, ALLSELECTED ( 'Cities'[City] ), [Total Orders] ),
            ALLSELECTED ( 'Date' )
        ) -- Calculate the total orders for the Top N cities
    VAR TopN_Orders =
        CALCULATE ( [Total Orders], KEEPFILTERS ( Top_Cities ) ) -- Calculate total orders for all selected cities
    VAR All_Orders =
        CALCULATE ( [Total Orders], ALLSELECTED ( Cities ) ) -- Calculate the total orders for cities that are not in the Top N
    VAR Other_Orders =
        All_Orders - CALCULATE ( [Total Orders], Top_Cities )
    VAR Result =
        SWITCH (
            TRUE (),
            NOT ISINSCOPE ( Cities[City] ), All_Orders,
            SELECTEDVALUE ( Cities[City] ) = "Others", Other_Orders,
            TopN_Orders
        )
    RETURN
        Result
    

2 Replies

  • I think you need to tweak the Top_Cities variable to use ALLSELECTED('Date')

    Top Orders | Cities =
    VAR N = 'Top N'[Top N Value] -- Get the top N cities by total orders
    VAR Top_Cities =
        CALCULATETABLE (
            TOPN ( N, ALLSELECTED ( 'Cities'[City] ), [Total Orders] ),
            ALLSELECTED ( 'Date' )
        ) -- Calculate the total orders for the Top N cities
    VAR TopN_Orders =
        CALCULATE ( [Total Orders], KEEPFILTERS ( Top_Cities ) ) -- Calculate total orders for all selected cities
    VAR All_Orders =
        CALCULATE ( [Total Orders], ALLSELECTED ( Cities ) ) -- Calculate the total orders for cities that are not in the Top N
    VAR Other_Orders =
        All_Orders - CALCULATE ( [Total Orders], Top_Cities )
    VAR Result =
        SWITCH (
            TRUE (),
            NOT ISINSCOPE ( Cities[City] ), All_Orders,
            SELECTEDVALUE ( Cities[City] ) = "Others", Other_Orders,
            TopN_Orders
        )
    RETURN
        Result
    
    • PBI12345's avatar
      PBI12345
      Icon for Helper I rankHelper I

      Hi John,

       

      This worked exactly as hoped. Thank you so much.