Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

dynamic top 5 + others

Hey everyone,

 

I wanted to create a pie chart with the percentage of tickets sold by each location. Since there are so many locations, I am looking for the top 5. Filter on visual by TopN isn't suitable, because the percentages shown are incorrect, cause it doesn't include the locations outside the top 5. So, therefore I am looking for the top 5 locations + others. 

 

I've created a Rank measure first:

RankLocations = 

RANKX(
CALCULATETABLE(distinct('SLA Cycles'[Location]), ALLSELECTED('SLA Cycles')),
CALCULATE([Tickets]),, DESC, Dense)
 
So, I assumed that creating a calculated column should work as well:
Top 5 locations + others =
VAR RankLocations = RANKX(
CALCULATETABLE(DISTINCT('SLA Cycles'[Location]), ALLSELECTED('SLA Cycles')),
CALCULATE([Tickets]),, DESC, Dense)
RETURN
IF(RankLocations <=5, 'SLA Cycles'[Location], "Others")
 
Unfortunately, that didn't work, and I don't understand why. I thought the blank rows could be an issue. I filled in those blanks with a text, but the result didn't change. The mentioned calculated column is just a copy of the column 'Location', hence it doesn't do anything. 
 
I hope someone does understand the underlying problem.
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hey v-kkf-msft ,

     

    Thank you so much for your reply and effort. The column is the same as the Location column, so the formula has no effect. 

    I came across a different solution from someone with the same question: 

    https://community.powerbi.com/t5/Desktop/Top-N-and-others-at-the-same-time/m-p/498687#M232598

     

    I created two unrelated tables:

    • a table with all the location names and a row with "Other locations"
      • Column: UNION(VALUES('SLA Cycles'[Location]), ROW("Location", "Other locations"))
    • a table to slice for the amount of locations shown, default is set to 5
      • Column: TopN locations= GENERATESERIES(1, 20, 1)
        Measure: TopN Value = SELECTEDVALUE('TopN locations'[Value], 5)
         
        Finally, I needed to create the measure:
        Top 5 + overige locaties = 
        var thetopn = CALCULATETABLE(TOPN('TopN locations'[TopN Value], VALUES('SLA Cycles'[Locations]), [Tickets]))
        var other = ROW("Location", "Overige locaties")
        var allTheRest = CALCULATE([Tickets], EXCEPT(VALUES('SLA Cycles'[Location]), thetopn))
        var theUnion = UNION(thetopn, other)
        RETURN
        SUMX(
        INTERSECT(Location, theUnion),
        var currentIterator = Location[Location]
        RETURN
        IF(
        Location[Location] <> "Other locations",
        CALCULATE([Tickets], 'SLA Cycles'[Location] = currentIterator),
        allTheRest
        )
        )
        I still don't understand why your and amitchandak formula didn't work as expected... The only other notable thing is that the measure 'Tickets' is a Distinctcount function; it counts the SLA codes, which is like this: DL-309 or DL-1588. And since we keep track of two important things, two rows with the same Code are created. So the column Location also returns two rows with the same Location name for one ticket. 

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try like

     


    Top 5 locations + others =
    VAR RankLocations = RANKX(
    ALLSELECTED('SLA Cycles'[Location]),
    CALCULATE([Tickets]),, DESC, Dense)
    RETURN
    maxx(values('SLA Cycles'[Location]), IF(RankLocations <=5, 'SLA Cycles'[Location], "Others"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey amitchandak , 

      Thanks for your reply. The formula returns a location which is ranked last... 

      The first part of the formule does the job 

      (RANKX(
      ALLSELECTED('SLA Cycles'[Location]),
      CALCULATE([Tickets]),, DESC, Dense))

       

      So I assume the formula after Return is not correct yet. I hope you've another suggestion. And if necessary, I can share a sample data set. 

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Please try the following formula:

     

    Top 5 locations + others = 
VAR RankLocations =
    RANKX (
        ALLSELECTED ( 'SLA Cycles'[Location] ),
        CALCULATE ( [Tickets] ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( RankLocations <= 5, 'SLA Cycles'[Location], "Others" )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    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
      4 years ago

      Hey v-kkf-msft ,

       

      Thank you so much for your reply and effort. The column is the same as the Location column, so the formula has no effect. 

      I came across a different solution from someone with the same question: 

      https://community.powerbi.com/t5/Desktop/Top-N-and-others-at-the-same-time/m-p/498687#M232598

       

      I created two unrelated tables:

      • a table with all the location names and a row with "Other locations"
        • Column: UNION(VALUES('SLA Cycles'[Location]), ROW("Location", "Other locations"))
      • a table to slice for the amount of locations shown, default is set to 5
        • Column: TopN locations= GENERATESERIES(1, 20, 1)
          Measure: TopN Value = SELECTEDVALUE('TopN locations'[Value], 5)
           
          Finally, I needed to create the measure:
          Top 5 + overige locaties = 
          var thetopn = CALCULATETABLE(TOPN('TopN locations'[TopN Value], VALUES('SLA Cycles'[Locations]), [Tickets]))
          var other = ROW("Location", "Overige locaties")
          var allTheRest = CALCULATE([Tickets], EXCEPT(VALUES('SLA Cycles'[Location]), thetopn))
          var theUnion = UNION(thetopn, other)
          RETURN
          SUMX(
          INTERSECT(Location, theUnion),
          var currentIterator = Location[Location]
          RETURN
          IF(
          Location[Location] <> "Other locations",
          CALCULATE([Tickets], 'SLA Cycles'[Location] = currentIterator),
          allTheRest
          )
          )
          I still don't understand why your and amitchandak formula didn't work as expected... The only other notable thing is that the measure 'Tickets' is a Distinctcount function; it counts the SLA codes, which is like this: DL-309 or DL-1588. And since we keep track of two important things, two rows with the same Code are created. So the column Location also returns two rows with the same Location name for one ticket.