Why topN function not working in Selectedvalues

[Anonymous]

HI 

I am trying to get top 3 customer based on Selected values, but does not working 

Top 3 Customers =

IF(
SELECTEDVALUE('2425'[SBUName])=MAX('2425'[SBUName]),
CALCULATE(
SUM('2425'[ValuesinLakhs]),
TOPN(3,ADDCOLUMNS(VALUES('2425'[Customer Name]),"@Test",MROUND(SUM('2425'[ValuesinLakhs]),5)
),
[@Test],DESC,'2425'[Customer Name], ASC)

)
)

[bhanu_gautam]

@Anonymous , Try using 

Top 3 Customers =
CALCULATE(
SUM('2425'[ValuesinLakhs]),
TOPN(
3,
SUMMARIZE(
'2425',
'2425'[Customer Name],
"@Test", SUM('2425'[ValuesinLakhs])
),
[@Test], DESC,
'2425'[Customer Name], ASC
),
'2425'[SBUName] = SELECTEDVALUE('2425'[SBUName])
)

[Anonymous]

Hi Bhanu,

Not getting change 

[Anonymous]

Thanks for the reply from bhanu_gautam , please allow me to provide another insight:

Hi, @Anonymous 

Your idea is excellent. Below are my sample data and alternative solutions, which I hope you find helpful:

1.Firstly, using measures:

Top 3 Customers =
VAR name1 =
    SELECTCOLUMNS (
        TOPN ( 3, ALLSELECTED ( '2425' ), '2425'[ValuesinLakhs], DESC ),
        "name", '2425'[Customer Name]
    )
RETURN
    IF (
        MAX ( '2425'[Customer Name] ) IN name1,
        SUMX (
            FILTER ( '2425', '2425'[Customer Name] IN name1 ),
            MROUND ( '2425'[ValuesinLakhs], 5 )
        ),
        BLANK ()
    )

Here are the final results:

Of course, I recommend modifying the following filters to enhance the data further:

2.Secondly, directly using Top N in the filters:

Next, rounding the measures:

Measure = MROUND(SUM('2425'[ValuesinLakhs]),5)

Here are the final results, which I hope will meet your needs:

Both of these options have their advantages and disadvantages, so you can choose based on your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Leroy Lu,

Thank you for giving best. here can we go with SBU wise topn,

If A  top 3.

B top 5.

C Top 10 LIke... 

[Anonymous]

Hi, @Anonymous 

Thank you for your swift response.

You can modify the measure as follows:

Top 3 Customers = 
VAR select1=MAX('2425'[SBUName])
VAR number1=SWITCH(TRUE(),select1="A",3,select1="B",5,select1="C",10,3)
VAR name1 =
    SELECTCOLUMNS (
        TOPN (number1, ALLSELECTED ( '2425' ), '2425'[ValuesinLakhs], DESC ),
        "name", '2425'[Customer Name]
    )
RETURN
    IF (
        MAX ( '2425'[Customer Name] ) IN name1,
        SUMX (
            FILTER ( '2425', '2425'[Customer Name] IN name1 ),
            MROUND ( '2425'[ValuesinLakhs], 5 )
        ),
        BLANK ()
    )

Incorporate the switch() function for evaluation.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.