Forum Discussion

brianlehr's avatar
brianlehr
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Percentage of rows with specific string

Easy question here (I think, as I haven't done PowerBI in a couple years and all my skills have left me):

 

I have a table like this

TeamStatus
Team1Red
Team1Green
Team2Red
Team2Yellow
Team2Red
Team2Green

 

My usecase is that I want to be able to count the number of rows that are of status Red, status Yellow, and status Green...and then have a separate multi-row card that shows percentage in each.  It should also be able to change based on what Team is selected, so I'm guessing I need a measure.

So for example, with no filters applied, it should show 50% Red, 17% Yellow, 33% Green

If I filter to only Team2, it should show 50% Red, 25% Yellow, 25% Green

Would also be cool to have a way to show % of a certain color for each team.

Need Help

4 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

    To show the Teamname add the below column to your table :

    TeamName = left(Sheet43[Team Status],5)
    Then create three separate Measure for each color :
    YellowRatio =
    Var Allcnt = COUNT(Sheet43[Team Status])
    Var Yellowcnt = CALCULATE(count(Sheet43[Team Status]),
                            SEARCH("Yellow",Sheet43[Team Status],1,0)
    )
    return COALESCE( Yellowcnt/Allcnt, 0)
    * Do this for other colors (change the color in the search function)
    - select 'Percentage' as a Format for particular measures.
    - use the measure in your Multi card Visual

     

    • brianlehr's avatar
      brianlehr
      Icon for Microsoft Employee rankMicrosoft Employee
      4 years ago

      Accepted the other answer since I tried it first, but I'm going to also try this to see if it gives more flexibility.  Never used the COALESCE command before.  Thanks!