cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Complex formula to provide counts and accurate percentages

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!

Super User

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors