How would I calculate the percentage of cells that have a value?

[mpearson89]

I am working on a dashboard for a Maven Analytics dataset that relates to parental leave offered by various companies. I want to calculate the percentage of companies that offer paid maternal and paternal leave. In the data set, each row is a company and the columns include the industry, weeks of paid maternity leave, weeks of paid paternity leave, weeks of unpaid maternity leave, and weeks of unpaid paternity leave.

My guess on the best way to do this is to determine the total number of cells in each column that have a value higher than 0 and then compare that to the total number of cells in the column, but I'm not sure how to do this. Any advice, or is there a better way I could do this? Or should I be doing this outside of Power BI in the first place, maybe in Excel?

[parry2k]

@mpearson89 try this, add the following measures, and do the same for maternity leave.

Total Count = COUNTROWS ( YourTable )

Unpaid Paternity Leave Count = CALCULATE ( [Total Count], YourTable[Unpaid Paternity Leave] = 0 )

% Paternity Leave = 
DIVIDE ( 
     [Unpaid Paternity Leave Count], 
     CALCULATE ( [Total Count], ALLSELECTED ( YourTable ) ) 
) 

Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. If you can spend time posting the question, you can also make an effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@mpearson89 can you paste the sample data in the table format with the expected output? One this is for sure, it can be easily computed in Power BI, and no precompute is required.

Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[mpearson89]

@parry2k Here is a screenshot of the data. The number in each cell represents the number of weeks of leave offered. So I basically want to know how many cells in each column have a 0 so I can determine the percentage of companies that do/don't offer paid leave.