Forum Discussion
PBI12345
Helper I
1 year agoDynamic 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...
- 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
johnt75
Super User
1 year agoI 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
Helper I
1 year agoHi John,
This worked exactly as hoped. Thank you so much.