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 i...
  • johnt75's avatar
    1 year ago

    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