How create a measure that counts a value in one column then divides by another

[rush]

Hi All

I would like to create a measure that will count a value in one column then divides by another.

I have tried to create the measure for Male & Female but it does not bring back the result by splitting by Race.

The measure should count per Race then divide by Staff Count (which I have a measure for).

Basically I would require 2 measures, one for Male & the other for Female which I can use in my table.

I have columns:

Race

Female% (Female % = CALCULATE([Female Count]/[Staff Count])

Male% (Male % = CALCULATE([Male Count]/[Staff Count])

Table currently:

Table

Result desired:

Result

[anupampandey]

Hi,

First you need to create the MaleCount_PerRace (Staff Count * Male%) & FemaleCount_PerRace (Staff Count * Female%). After that you create:

Race_Male = MaleCount_PerRace / sum(Staff Count)

Race_Female = FemaleCount_PerRace / sum(Staff Count)

Hope this is what you want.

Thanks,

Anupam

[rush]

Hi @anupampandey

Thank you very much.

I created those as mesaures but it does not bring back the results I require.

[anupampandey]

Hi @rush,

Could share the sample data from your raw data please? Or you can create the calculated column instead of measure and check whether you are getting the desired result or not.

Thanks,

Anupam

[rush]

@anupampandey

It is bringing back 100% for all the males.

Thanks.

Please see raw data below in link:

RAW Data

[anupampandey]

Hi,

I have created a summarize table based on the raw data. This table is giving me the desired result. I am also trying to do the same without summarizing the table into new table.

Race %Age = SUMMARIZE(Staff,Staff[Race],"TotalStaff",SUM(Staff[Staff Count]),"Male",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Male")),"Female",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Female")))

above is the statement I have used to summarize the raw table and than created the 2 measures (calculted column):

Race Female % = 'Race %Age'[Female]/SUM('Race %Age'[TotalStaff])
Race Male % = 'Race %Age'[Male]/SUM('Race %Age'[TotalStaff])

Once I'll get the desired result in raw table will keep you posted till the time you can use this solution.


Thanks,
Anupam

[rush]

@anupampandey Thank you very much.

The summarized table helped a lot but I forgot to mention is it possible to add a filter for that entire table to only include Active Team Members?

I have a column called Team Member Status which can be used to filter the summarized table to only bring back Active Team Members.

[anupampandey · Accepted solution]

Hi @rush,

Yes you can put the filter while summarizing the table. Use below syntax:

Race %Age = CALCULATETABLE(SUMMARIZE(Staff,Staff[Race],"TotalStaff",SUM(Staff[Staff Count]),"Male",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Male")),"Female",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Female"))),FILTER(Staff,Staff[Team Member Status]="Active")

I hope this will work for you to get the desired result. If it works please accept it as a solution.

Thanks & Cheers!!!

Anupam

[rush]

Hi @anupampandey

Thank you. 

I managed to add the filters but to each column needed.

Will use your formula as well.

Staff EE = SUMMARIZE(Staff,Staff[Race Group],
"TotalStaff",CALCULATE(SUM(Staff[Staff Count]),Staff[Team Member Status] = "Team Member"),
"Male",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Male"),Staff[Team Member Status] = "Team Member"),
"Female",CALCULATE(SUM(Staff[Staff Count]),FILTER(Staff,Staff[Gender]="Female"),Staff[Team Member Status] = "Team Member"))