Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Why topN function not working in Selectedvalues

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)

)
)

 

 

Need Help

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Bhanu,

      Not getting change 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.