Skip to main content
Showing results for 
Search instead 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.

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. table.PNG


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
Super User

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

Helpful resources

Microsoft Fabric Learn Together

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.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors