Forum Discussion

SamTrott's avatar
SamTrott
Icon for Helper I rankHelper I
6 years ago

Replicate chart in Excel in Power BI

In Excel I can create a chart that looks like this, it shows me the top 5 Categories along with the top 5 sub categories for each category. Note how the category labels appears underneath as a kind of parent header rather than on every sub category label

 

However when I try to make this in Power BI the ranking seems all over the place. If I have just Category in the axis than the rank I've setup displays the top Five just fine, likewise for Sub-category. But when I have both Category and Sub-Category it goes all wonky. Additionally rather than show a header label for Category it adds the Category label to every Sub-Category label making it far messier.   

 

I've seen BI charts using dates display it similar to how Excel does above so I assume such a thing is possible.

 

The Ranks I'm using are just 

Rank Category by closed incidents = RANKX(ALL(IncidentsClosed[Category]),[Count of Incidents Closed])
&
Rank Sub-Category by closed incidents = RANKX(ALL(IncidentsClosed[Sub-Category]),[Count of Incidents Closed])
 
I assume these are going to need changing.
Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I would say to turn off "concatenate labels" in X axis but that appears to have zero effect, I wonder if it is broken?

    • SamTrott's avatar
      SamTrott
      Icon for Helper I rankHelper I
      6 years ago

      Indeed I've tried that and it didn't work. If its not broken then perhaps its something to do with my fields as like the example you provided its dates, I've not seen it work with text based fields before, though I would think it used the same logic. 

       

      I even tried creating a heiarchy using the two fields but it didn't seem to have any affect on how they displayed. 

      • SamTrott's avatar
        SamTrott
        Icon for Helper I rankHelper I
        6 years ago

        Okay I've managed to fix the labels. If I place Category and Sub-Category into a heirarchy and then turn Concatenate off it fixed the labels however the Rank's still appear to not be working as intended.

         

        I need top 5 cateogries and top 5 sub categories of each of the top 5 categories as shown in the Excel image in the main post. Current rank measures are written above for refernece, presumably they need some modifying.