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'[ValuesinLa...
Need Help
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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      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.