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

Reply
aussiecro
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:

aussiecro_0-1716442365375.png

 

- "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

Managed to solve it

 

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

aussiecro_1-1716526711836.png

so it reads:

aussiecro_0-1716526676339.png

it now shows correctly:

aussiecro_2-1716526752502.png

 

thanks

 

 

View solution in original post

13 REPLIES 13
danextian
Super User
Super User

Hi @aussiecro 

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.

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

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.

@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.

Hi

 

I have tried the formula:

 

aussiecro_0-1716449705685.png

 

when i run it though its blank:

aussiecro_1-1716449746644.png

 

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

aussiecro_2-1716450029490.png

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

 

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

aussiecro_3-1716450084440.png

 

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

 

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

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.

Hi Danexian

 

That seemed to work better. I now get this:

 

aussiecro_0-1716459618726.png

 

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

 

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:

 

aussiecro_0-1716464943223.png

 

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:

 

aussiecro_1-1716465041181.png

 

 

Managed to solve it

 

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

aussiecro_1-1716526711836.png

so it reads:

aussiecro_0-1716526676339.png

it now shows correctly:

aussiecro_2-1716526752502.png

 

thanks

 

 

Hi Danextian

 

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

 

cheers

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors