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 su...
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. 
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.