Formula to Calculate Percentages for Trending Headcounts with Gender Breakdown

[DanielG1987]

Hi All,

I seem to be stuck on a DAX formula that I could use your help on. I'm still a fairly new PowerBI user and I'm learning as I go. Please let me know if I'm missing any information that could help solve this issue.

• My dataset includes data for both regular and contingent team members
• All of the data is on one Excel sheet that I'm importing into PowerBI
• Regular team members provide their gender while contingent workers do not
• The data has a column for effective month from January to June
• I'm creating a line chart that shows trending headcount makeup from January to June
  • For example - January: 70% female, 25% male, 5% undeclared, and so on and so forth
• However, the formula I came up with is calculating the percentages based on all workers - regular and contingent - and is therefore taking the "blank" gender field into consideration when calculating
  • This means it's now showing - January: 50% female, 20% male, 10% undeclared, and 20% (blank)

My question: how do I rewrite this formula so that it excludes the (blank)s from the calculation? Below, please find the formula I'm using. I'm also attaching a sample dataset that I hope will help. My table is titled "All Data", for reference. Thank you!

Formula = divide(count('All Data'[Employee ID]),calculate('All Data'[Employee ID]),all('All Data'[Gender])))

Month	Effective Date	Employee ID	Employee Type	Gender
January	1/1/2023	C5043	Contingent
January	1/1/2023	C8676	Contingent
January	1/1/2023	C3086	Contingent
January	1/1/2023	8639	Regular	Female
January	1/1/2023	8638	Regular	Female
January	1/1/2023	8637	Regular	Male
January	1/1/2023	8640	Regular	Male
January	1/1/2023	8641	Regular	Male
January	1/1/2023	8642	Regular	Male
January	1/1/2023	8643	Regular	Female
February	2/1/2023	8888	Regular	Female
February	2/1/2023	8889	Regular	Female
February	2/1/2023	8887	Regular	Female
February	2/1/2023	8886	Regular	Male
February	2/1/2023	C12345	Contingent
February	2/1/2023	C12346	Contingent
February	2/1/2023	C12347	Contingent
March	3/1/2023	8880	Regular	Male
March	3/1/2023	8881	Regular	Male
March	3/1/2023	8882	Regular	Female
March	3/1/2023	C234567	Contingent
March	3/1/2023	C234568	Contingent

UPDATE:

Hi all,

I thought it would be a good idea to provide a visual of my chart to show what I'm getting as a result of my formula above:

My issues is that I do NOT want that blank data counted in the percentage calculations. If I remove the blank data in the visual filters, it simply "hides" the blank line from the chart above, but it still takes the blank data into consideration when performing the calculations.

[Ashish_Mathur]

Hi,

Based on the sample dataset that you have shared, show the expected result.

[DanielG1987]

Hi Ashish, Based on the sample dataset, I would want to see the following:

January | Female: 42.85%, Male: 57.15%

My current calculation is giving me the below.

January | Female: 30%, Male: 40%, (blank): 30%

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[DanielG1987]

Hi @Ashish_Mathur 

Thank you for this! I just updated my original post. My issue is that I'm wanting this data on a line chart to show trending percentages. While your solution does accurately count the data while excluding blanks, it's not something I'm able to put on a line chart. Any thoughts?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Clearly show the problem there.

[DanielG1987]

Here you go! I tried to attach the file, but I got an error message saying that pbix was not supported. Hopefully the link below works.

Sample PBI 

[Ashish_Mathur]

You have not at all followed my instructions in the PBI file tht i shared with you. I have created a Calendar Table which you have not.  Have you even opened/studied my file? 

[ryan_mayu]

@DanielG1987 

is this what you want?

Measure = COUNT('Table'[Employee ID])/CALCULATE(COUNT('Table'[Employee ID]),ALL('Table'),'Table'[Gender]<>"")

[DanielG1987]

@ryan_mayu - thanks for taking a look! I tried the formula and it's still counting (blank) data, but it looks like it's now giving percentages based on the overall numbers, rather than based on January alone. For example, January is now showing the below.

(blank): 6.08%

Female: 11.56%

Male: 5.07%

Not Declared: 0.22%

[ryan_mayu]

is your expected output based on the sample data you provided? if not, pls update the sample data. Otherwise, it's hard for me to provide a better solution

[DanielG1987]

Hi @ryan_mayu 

I thought the original dataset I provided was accurate, but it may not have been. I have updated the chart to reflect slightly more accurately what I'm working with. I also added a screenshot to show the result I'm getting using my current formula. Any thoughts?

[ryan_mayu]

still not clear about this. let's just focus on Jan data. What's your expected output for Jan data?

Month	Effective Date	Employee ID	Employee Type	Gender
January	1/1/2023	C5043	Contingent
January	1/1/2023	C8676	Contingent
January	1/1/2023	C3086	Contingent
January	1/1/2023	8639	Regular	Female
January	1/1/2023	8638	Regular	Female
January	1/1/2023	8637	Regular	Male
January	1/1/2023	8640	Regular	Male
January	1/1/2023	8641	Regular	Male
January	1/1/2023	8642	Regular	Male
January	1/1/2023	8643	Regular	Female