How would I calculate the percentage of cells that...

mpearson89 · ‎04-24-2023

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 · ‎04-26-2023

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k · ‎04-24-2023

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mpearson89 · ‎04-26-2023

@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.