Forum Discussion
Helper VHow create a measure that counts a value in one column then divides by another
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
Helper Vanupampandey 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.
Helper IIIHi 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"))