Complex formula to provide counts and accurate percentages

Anonymous — Mon, 22 Jun 2020 15:03:01 GMT

Hi All-

I'm working on the intersection between two tables.

One table is a list of employees. There is one line per employee, there is a region column, and a status column (active or inactive).

The other table is a list of certifications. There is one line per certification, there is a platform column (i.e. scrum, c+), there is a name column (matching names on the other table), and a status column (active or inactive).

I'm trying to create essentially a pivot table. The columns across the top are the platforms, and each platform has a count and a percentage (percentage of employees in that region, not total). The rows are the regions. This should only reflect active employees by country (using the first table as a reference).

This is the formula I'm using right now, but it's providing inaccurate percentages and I can't figure out why:

Percentage Certifications New =

// get distinct count of employees on certifications list witn only active employees

var individuals = DISTINCTCOUNT('Certifications Active Only'[Name])

// get count of active employees on employee list

var total = COUNTAX('GlobalTeam (2)',[Status]="Active")

// get count of employees by country !!ONLY one of the two options!!

// OPTION 1 var countryTotal = COUNT('GlobalTeam (2)'[Country])
// OPTION 2 var countryTotal = COUNT('Certifications Active Only'[Country])

// get percentage of certified employees in the country

var percentage = ROUND(((individuals/countryTotal)*100),0)

// return all values for auditing purposes. Once complete only percentage will be returned

return "percent"&percentage&"!total"&total&"!ind"&individuals&"!country"&countryTotal

Option 1 returns the same countryTotal for every single row, including the total row. This is not correct. Option 2 returns a count of every certification line relevant to the platform, where multiple lines can reflect a single individual. The 61 in the screenshot below is more rows than we have employees with BP certifications.

Here's a small example screenshot of the table reflecting option 2, for help explaining it.

I can't do a distinct count on the names in the certifications list, because then numberator and denominator will be the result of the same function so everything will be 100%. Please help!!

Thank you!

Re: Complex formula to provide counts and accurate percentages

lbendlin — Mon, 22 Jun 2020 22:32:44 GMT

You are describing two "dimension" tables (employees and certifications) but it seems you leave out the "fact" table (employee certifications) ?

topic Re: Complex formula to provide counts and accurate percentages in DAX Commands and Tips

Complex formula to provide counts and accurate percentages

Re: Complex formula to provide counts and accurate percentages