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

[aussiecro]

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

[danextian]

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

[aussiecro]

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

[danextian]

instead of

SUM ( tbl[divisioncount] )

just use [divisioncount]

[fahadqadir3]

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

[aussiecro]

Hi

I have tried the formula:

when i run it though its blank:

[aussiecro]

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)

[aussiecro]

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

[danextian]

Use this

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

[aussiecro]

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

[aussiecro]

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:

[aussiecro · Accepted solution]

Managed to solve it

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

so it reads:

it now shows correctly:

thanks

[aussiecro]

Hi Danextian

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

cheers

[danextian]

If you are trying something like this

not competent - 60%

competent - 40 %

use the first approach.