DAX Formula for % of Emps

[Lamda]

Hello All,

I have a scenario to where I am putting employees into buckets of when someone has initiated an action and when they have not. So say I have 1000 emps and lets say 600 initiated an action and 400 did not. I need to show in my report in Excel my bucket of 0 or 1+ Actions - so this would be one column, and then in the next column I want to show the # of Actions and then the % of those employees off of the total # of employees. So it would look something like:

Actions-----Values
0------------400
0------------40%
1------------600
1------------60%

In my SSAS/DAX data model I have the count of employee's as 1000, and created a column for the Buckets showing if an employee had an Action or not. So I have those just fine - my issue is creating the DAX formula to come up with the 40%/60% in my example above. In a logical way its simple as its The # of Emps with an action / total # of Emps. But how do I do that in my model? I have an Emps column where I do a count of all Emps. I have an Action column and I have created a Sum Measure of those. As I stated, I created a Bucket column to determine whether the Emp had an action or not. I tried to sum that up and then divide it by the Emps total but that didn't remotely work, lol.

Any suggestions would be appreciated!

 

[Matt Allington]

Your employee count measure responds to the natural filtering behaviour of the pivot table. That is why you get 400 and 600. What you need is a variation of that measure that does not respond to the filtering of the Pivot - in other words one that will always show the total of all employees. You can do that like this

=CALCULATE([original measure],ALL(TableName[action column]))

 

[Lamda]

Thanks for your reply. I guess my issue is that in the formula you put above - what I end up with is the % of emps with Actions ends up being 100% while the % of emps with no actions ends up being 0. What I am looking for in my example above is to get a percentage of 40% of emps with no actions and 60% for both - so who so I do both in the same formula?

 

[Matt Allington]

It is so much easier to help if you can post a sample workbook with your current (not working) formulas. If you can do that (say with Dropbox) I will take a look for you