Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Need help with DAX for Top N values

Hi, 

I have written a DAX to display the Topmost % of certain categories from the data table
For ex - 20% of my followers live in London, 85% identify themselves as Male etc.
This is my DAX :

 

Top City KPI =
VAR CityTable =
    SUMMARIZE(
        'Data',
        'Data'[City],
        "@TotalFAN", COUNT('Data'[FAN])
    )

VAR TotalFAN = CALCULATE(COUNT('Data'[FAN]))
VAR TopRow = TOPN(1, CityTable, [@TotalFAN], DESC)
VAR TopCity = SELECTCOLUMNS(TopRow, "TopCity", 'Data'[City])
VAR TopCityCount = SELECTCOLUMNS(TopRow, "TopCount", [@TotalFAN])

RETURN
  FORMAT(DIVIDE(TopCityCount, TotalFAN), "##.#%") & " reside in " & FIRSTNONBLANK(TopCity, [TopCity])

and here is how it is displayed in visual(CARD) -

However, I would like to change the DAX to display not just the Topmost but Top 5 or 10 instead. I'm finding it difficult to display the same.
Can you please help ?

 

Thanks in advance.

Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    Hi Anonymous 

    Can you please try the below steps.

    1. First, create a table to select the top values
               By entering manually ( enter data option and enter a value for how much you want in top)

    2.  Use below DAX to find the top N values

                   

    Top Cities KPI =
    VAR N = SELECTEDVALUE('Top'[top])
    VAR CityTable =
        SUMMARIZE(
            'Table (2)',
            'Table (2)'[City],
            "TotalFAN", COUNT('Table (2)'[FAN ID])
        )

    VAR TotalFAN = CALCULATE(COUNT('Table (2)'[FAN ID]))

    VAR TopCitiesTable =
        TOPN(N, CityTable, [TotalFAN], DESC)

    VAR ResultTable =
        ADDCOLUMNS(
            TopCitiesTable,
            "Percentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")
        )

    VAR ResultString =
        CONCATENATEX(
            ResultTable,
            [Percentage] & " in " & [City],
            ", "
        )

    RETURN
        ResultString
     
     
     
    3. use as a silcer the table you created for select top N values.


     

    If this answers your questions, kindly accept it as a solution and give kudos.
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    Hi Anonymous 

    Can you please try the below DAX?

    I have modified only in (VAR ResultTable and VAR ResultString)

     

    Top Cities KPI =
    VAR N = SELECTEDVALUE('Top'[top])
    VAR CityTable =
    SUMMARIZE(
    'Table (2)',
    'Table (2)'[City],
    "TotalFAN", COUNT('Table (2)'[FAN ID])
    )

    VAR TotalFAN =
    CALCULATE(COUNT('Table (2)'[FAN ID]))

    VAR TopCitiesTable =
    TOPN(N, CityTable, [TotalFAN], DESC)


    VAR ResultTable =
    ADDCOLUMNS(
    TopCitiesTable,
    "NumericPercentage", DIVIDE([TotalFAN], TotalFAN),
    "FormattedPercentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")
    )


    VAR ResultString =
    CONCATENATEX(
    ResultTable,
    [FormattedPercentage] & " in " & [City],
    ", ",
    [NumericPercentage], DESC
    )

    RETURN
    ResultString



    If this answers your questions, kindly accept it as a solution and give kudos.

6 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

    Can you please try the below steps.

    1. First, create a table to select the top values
               By entering manually ( enter data option and enter a value for how much you want in top)

    2.  Use below DAX to find the top N values

                   

    Top Cities KPI =
    VAR N = SELECTEDVALUE('Top'[top])
    VAR CityTable =
        SUMMARIZE(
            'Table (2)',
            'Table (2)'[City],
            "TotalFAN", COUNT('Table (2)'[FAN ID])
        )

    VAR TotalFAN = CALCULATE(COUNT('Table (2)'[FAN ID]))

    VAR TopCitiesTable =
        TOPN(N, CityTable, [TotalFAN], DESC)

    VAR ResultTable =
        ADDCOLUMNS(
            TopCitiesTable,
            "Percentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")
        )

    VAR ResultString =
        CONCATENATEX(
            ResultTable,
            [Percentage] & " in " & [City],
            ", "
        )

    RETURN
        ResultString
     
     
     
    3. use as a silcer the table you created for select top N values.


     

    If this answers your questions, kindly accept it as a solution and give kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This is very helpful! thank you.
      How do I order the answers in descending order of the %s and in separate lines instead of commas in the result set?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      This is very helpful! thank you.
      How do I order the answers in descending order of the %s and in separate lines instead of commas in the result set? mdaatifraza5556 

      • mdaatifraza5556's avatar
        mdaatifraza5556
        Icon for Super User rankSuper User
        1 year ago

        Hi Anonymous 

        Can you please try the below DAX?

        I have modified only in (VAR ResultTable and VAR ResultString)

         

        Top Cities KPI =
        VAR N = SELECTEDVALUE('Top'[top])
        VAR CityTable =
        SUMMARIZE(
        'Table (2)',
        'Table (2)'[City],
        "TotalFAN", COUNT('Table (2)'[FAN ID])
        )

        VAR TotalFAN =
        CALCULATE(COUNT('Table (2)'[FAN ID]))

        VAR TopCitiesTable =
        TOPN(N, CityTable, [TotalFAN], DESC)


        VAR ResultTable =
        ADDCOLUMNS(
        TopCitiesTable,
        "NumericPercentage", DIVIDE([TotalFAN], TotalFAN),
        "FormattedPercentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")
        )


        VAR ResultString =
        CONCATENATEX(
        ResultTable,
        [FormattedPercentage] & " in " & [City],
        ", ",
        [NumericPercentage], DESC
        )

        RETURN
        ResultString



        If this answers your questions, kindly accept it as a solution and give kudos.