Percentage of rows with specific string

[brianlehr]

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

Team	Status
Team1	Red
Team1	Green
Team2	Red
Team2	Yellow
Team2	Red
Team2	Green

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.

[ryan_mayu · Accepted solution]

@brianlehr 

pls create a status table 

and create a measure

Measure = CALCULATE(COUNTROWS('Table'),'Table'[Status]=max('Table (2)'[status]))/COUNTROWS('Table')

[brianlehr]

This works, thank you!

[MahyarTF]

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]

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!