I need to show No 1 Brand which has most sales value. For this I am making a measure where brands are ranked and then I am selecting one brand using TOP N (1) . The measure is giving accurate result most of the time but sometimes it it giving 2 numbers. But its not consistent. How to get rid of the issue. My measures are as follows,
Top Most Customer =
VAR pers =
SELECTEDVALUE ( 'Perspective Dim'[Perspective] )
RETURN
CONCATENATEX (
CALCULATETABLE (
TOPN (
1,
FILTER (
ALL ( 'Customer Dim'[CONSOLIDATED_GROUP_NAME] ),
NOT ( ISBLANK ( 'Customer Dim'[CONSOLIDATED_GROUP_NAME] ) )
&& NOT ( ISBLANK ( [vs LY] ) )
),
[CustomerRankDesc], ASC
)
),
'Customer Dim'[CONSOLIDATED_GROUP_NAME] & "(" & [vs LY Formatted] & ")",
", "
)
=====================================================================
CustomerRankDesc =
VAR CustomerName =
SELECTEDVALUE ( 'Customer Dim'[CONSOLIDATED_GROUP_NAME] )
RETURN
IF (
NOT ( ISBLANK ( [Total Value] ) ) && NOT ( ISBLANK ( CustomerName ) ),
RANKX ( ALL ( 'Customer Dim'[CONSOLIDATED_GROUP_NAME] ), [vs LY],, DESC, DENSE )
)
=====================================================================
vs LY =
DIVIDE ( ( [Selected Perspective] - [LY] ), [LY], 0 )
