topic Re: Dynamic Top N calculation for time series visual in DAX Commands and Tips

Dynamic Top N calculation for time series visual

PBI12345 — Mon, 16 Dec 2024 06:28:01 GMT

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

Re: Dynamic Top N calculation for time series visual

johnt75 — Mon, 16 Dec 2024 09:55:38 GMT

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

Re: Dynamic Top N calculation for time series visual

PBI12345 — Mon, 16 Dec 2024 10:33:26 GMT

Hi John,

This worked exactly as hoped. Thank you so much.