Question Measure - Count Status

[franorio]

Hello everyone!!!

I´m a rookie in Power BI, need help to figure out how to do the following:

I have a column named ¨Status¨, values on this column are: OK, Completed, In Progress, Not Trained, N/A. 

If i use the COUNT option for the colum values i get the total for the colmn, and not for each status.

So i guess i need to create a measure. What measure should I use, if i want to calculate the count for each one?

After this I need to create a measure to divide each one for the total to get a KPI. so I guess i should create a measure for each value of the possible status? Is this right? 

Appreciate your help. 

Best regards!

[danrmcallister]

If you had another table that contained your possible values for your statuses (OK, Completed, In Progress, Not Trained, N/A) you could add a column to that that 2nd table (Table2) to count the instances of each status in table 1 with the formula as follows.

Count of Status = CALCULATE(COUNTA(TABLE1[Status]),FILTER(ALL(TABLE1),TABLE1[Status]=TABLE2[Status]))

Where "Status" is the name of the status in either table and Count of Status is your calculation.  You can then create visuals off of that.

You could then create an additional column to calculate the ratio of a status to all instances with:

Ratio of Status = TABLE2[Count of Status] / SUM(TABLE2[Count of Status)

Benefit is that you'd have fewer measures to work with, but if you want to build multiple KPI cards you'll need to apply filters and name the cards accordingly.

[Sean · Accepted solution]

@franorio

If you do want to calculate individually each STATUS here's what each MEASURE would look like...

Number Completed = COUNTROWS ( FILTER ( TableName, TableName[Status] = "Completed" ) ) + 0

or

Number Completed = CALCULATE ( COUNTA ( TableName[Status] ), TableName[Status] = "Completed" ) + 0

The + 0 at the end is optional but it will help you avoid seeing blank() if you are using the Measure in a Card Visual.

In a card it will return blank() not zero if you don't add the + 0

It actually really depends where you'll use the Measure (in some cases you'll actually want to remove it or have a duplicate one with the + 0 and one without because on different pages you may use the Measure in a different Visual)

Hope this helps!

EDIT:

COUNTROWS ( Table ) = COUNTA ( Table[Column] ) + COUNTBLANK ( Table[Column] )

COUNTROWS - counts all rows

COUNTA - will not count blanks

COUNTBLANK - only blanks

https://msdn.microsoft.com/en-us/library/ee634956.aspx

[franorio]

Sean, thanks again for your reply and solution, i have a new question for you related to this matter, 

Your formula worked great for me to calculate the total of statuses for the total zone. 

The zone under analysis is (LAS), wich is conformed by 7 countries, Argentina, Bolivia, Colombia, Ecuador, Peru, Paraguay & Uruguay. I used your formula and was able to create measures for each status, and with this information created the KPI i wanted. Also add a filter by country and another one by Entity to have a visual break down for each status, in each country and each area. 

I would like to create a table with static information by country.  How should it be the formula to count the status by each country. Lets say create a measure to bring the completed, for each country. Where should i apply the filter in the formula?

Thanks again!

Best regards!

[franorio]

Thanks Sean, this was what i was looking for!!! Thank you all for your time!!!

[Phil_Seamark]

Try dragging the Status column to the AXIS of a visual and then add the measure you created to the VALUES area.

This should give you the break down you are after,

In terms of the KPI, have a look at the Quick Calc feature here.  

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-feature-summary/#analytics

Otherwise it can be done with a DAX measure.  Just give us a sample set of data and we can help you.