Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Calculate percentage based on categories in one column and different values in another column

Hi

I currently have the following information in a table:

- "Division Description" is a table that has data as to where the user is located

- "DivisionCount" is a measure i made up to see how many users are in each of those divisions

- "Competency level" is a table that only has 2 values "Competent" and "Not competent".

I'm trying to get a percentage to show how many users are "Competent" vs "Not competent" for each of those divisions

Cheers

1 ACCEPTED SOLUTION
New Member

Managed to solve it

I added the Exclusion column to the measure which is now accurate:

so it reads:

it now shows correctly:

thanks

13 REPLIES 13
Super User

Are you trying divide competent over not competent or get the percentage of competent and not competent per division? Not sure which one you're trying to achieve so try these measures:

``````DIVIDE (
SUM ( tbl[divisioncount] ),
CALCULATE ( SUM ( tbl[divisioncount] ), ALLEXCEPT ( tbl, tbl[division] ) )
)
``````
``````DIVIDE (
CALCULATE ( SUM ( tbl[divisioncount] ), tbl[competencylevel] = "Competent" ),
CALCULATE ( SUM ( tbl[divisioncount] ), tbl[competencylevel] = "Not Competent" )
)

``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
New Member

Hi Danetian

I have tried:

DIVIDE ( CALCULATE ( SUM ( tbl[divisioncount] ), tbl[competencylevel] = "Competent" ), CALCULATE ( SUM ( tbl[divisioncount] ), tbl[competencylevel] = "Not Competent" ) )

however "tbt[divisioncount]" is not a table but a measure (so it doesn't allow me to select it)

cheers

Super User

instead of

``SUM ( tbl[divisioncount] )``

just use [divisioncount]

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Responsive Resident

@aussiecro Remove the aggregate function in this case 'SUM' and table name 'tbl' before the Measure name.

`DIVIDE ( CALCULATE ( [divisioncount] , tbl[competencylevel] = "Competent" ), CALCULATE ( [divisioncount] , tbl[competencylevel] = "Not Competent" ) )`

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

New Member

Hi

I have tried the formula:

when i run it though its blank:

New Member

Realised the query was wrong with what to look against. changed it to:

(should of been "Competent" and "NotCompleted")

i now get some figures, but they dont look right:

Has the same percentage figures and they are wrong (for example crushing should be 71.6% competent and 28.4% Not Completed)

New Member

I think whats needed is a formula that:

Gets total number of users for the division (E.g. "Construction" has 1000 people), then find out how many are "Competent" vs "NotCompleted" (E.g. 600 "Competent" and 400 "NotCompleted" for "Construction division), then divide each with the sum to get the percentage:

(600/1000)*100 (Competent) = 60%

(400/1000)*100 (NotCompleted) = 40%

But do this for each division

Super User

Use this

``````DIVIDE (
[divisioncount],
CALCULATE ( [divisioncount], ALLEXCEPT ( tbl, tbl[division] ) )
)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
New Member

Hi Danexian

That seemed to work better. I now get this:

It seems to be a little out in the percentages though. If i look at Construction for example, we have 1072 total users. Competent therefore should read 768/1072 * 100 = 71.64% (it shows 70.20% though), and 304/1072 * 100 = 28.35% (it shows 27.79%) ?

the only accurate one is the crushing one

cheers

New Member

I think i know why this might be occurring.

We have a page filter applied where there is a column we are excluding people from the report. If the Excluded Column has "Yes", then they are excluded.

the measure doesn't seem to be adhering to the filter:

can we have it adhere to the page filter so it reads (100%) even though its excluded those people.

If i do this in a pie chart for a division (for example "Construction") it seems to give me the correct percentage witht he excluded filter applied:

New Member

Managed to solve it

I added the Exclusion column to the measure which is now accurate:

so it reads:

it now shows correctly:

thanks

New Member

Hi Danextian

I am trying to get the percentage of competent and not competent per division?

cheers

Super User

If you are trying something like this

not competent - 60%

competent - 40 %

use the first approach.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors