using a % of column total value to create a new measure

[claret_mug]

Hello

I'm completely new to Power BI and I'm wondering if I can create a measure based on values that are being shown as a % of column total. I've spent ages trying wo figure out how to do this. 

My two measures are

Female Progression = CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Gender]="Female",Table1[Residency Home EU Overseas]="Home")

and

Male Progression = CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Gender]="Male",Table1[Residency Home EU Overseas]="Home")

These measures are in a matrix and broken down by a column called 'Progressed?' which tells me who has/hasn't progressed. When displayed in my matrix the values (as % of column total) are male progression=79.1% and female progression=85.5% and now I need a calculation that will show me that that percentage point difference is 6.4.

Would anyone know how to do this?

Many thanks

Laura 

[ryan_mayu]

@claret_mug 

please try to calculate the % by measure

female%=
VAR Female Progression = CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Gender]="Female",Table1[Residency Home EU Overseas]="Home")
VAR TOTAL=CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Residency Home EU Overseas]="Home")
RETURN DIVIDE(FEMALE PROGRESSION, TOTAL)

MALE%=
var Male Progression = CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Gender]="Male",Table1[Residency Home EU Overseas]="Home")
VAR TOTAL=CALCULATE([Total students],'Table1'[Collaborative Provision Indicator]="Non-collaborative",Table1[Course length 1 year or less]="No",Table1[Programme Level]="UG",Table1[Course Mode(FT/PT)]="Full-Time",Table1[Entry Route]="UCAS/GTTR",Table1[Programme Year]="One",Table1[Residency Home EU Overseas]="Home")
RETURN DIVIDE(Male PROGRESSION, TOTAL)

then create a measure : female%-male%

[amitchandak]

@claret_mug , Can you share sample data and sample output in table format?

[parry2k]

@claret_mug create another measure for the difference if I understood your question correctly and you can use this new measure in your visuals.

Difference = [Female Progression] - [Male Progression]

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[claret_mug]

Hi @parry2k thanks so much for your reply. The problem with this is that is just gives me the number difference (598), rather than the percentage point difference which is what I need (6.4%)

Laura 

[harshnathani · Accepted solution]

HI @claret_mug ,

Create a Total Measure

VAR TOTAL =
CALCULATE (
    [Total students],
    'Table1'[Collaborative Provision Indicator] = "Non-collaborative",
    Table1[Course length 1 year or less] = "No",
    Table1[Programme Level] = "UG",
    Table1[Course Mode(FT/PT)] = "Full-Time",
    Table1[Entry Route] = "UCAS/GTTR",
    Table1[Programme Year] = "One",
    Table1[Residency Home EU Overseas] = "Home"
)

Create these Measures

Female % = Divide (Female Progression, Total)

Male % = Divide (Male Progression, Total)

Then create a final measure

% Change = Male% - Female%

Regards,

Harsh Nathani

[claret_mug]

Hi @harshnathani thank you so much. I've managed to get what I need 🙂