Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
spuri_78
Regular Visitor

Stuck on Divide Measure total

Hi guys,

 

I'm stuck at calculating Divide function. I know it should be simple. Below is the output table

 

GenerationHeadcountTurnoverTurnover rate of Generation% of Total Workforce% of Total Turnover
Generation Z4836.3  
Gen X1218.3  

 

I have the following measures in place.

 

Perm Headcount = CALCULATE(COUNTROWS(FILTER('T Emp,'T Emp'[Emp Type]="Perm"))

Turnover Perm Headcount = CALCULATE(COUNTROWS(FILTER('Turnover,'Turnover'[Emp Type]="Perm"))+0)

Attrition rate of Generation = FORMAT(DIVIDE([Turnover Perm Headcount],[Perm Headcount]),"0.0%)

 

I'm stuck with calculating  % of Total Workforce and % of Total Turnover

 

% of Total Workforce should be 48/Total Headcount and % of Total Turnover 3/Total Turnover

 

Any help with this will be greatly appreciated.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @spuri_78 ,

 

I reviewed this post and it seems the problem has not been solved yet.

I reproduced it based on your description.

T Emp table:

Emp TypeGeneration

Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
ABC Generation Z
ABC Generation Z
ABC Gen X
ABC Gen X
ABC Gen X

 

Turnover table:

Emp TypeGeneration

Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Gen X
ABC Generation Z
ABC Generation Z
ABC Gen X
ABC Gen X

 

For the table visual you want, it is suggested to create a dim table below.

Generation table:

Generation

Generation Z
Gen X

Relationships:

vstephenmsft_0-1726648010754.png

Here's what you're getting so far.

vstephenmsft_1-1726648028231.png

Tips: You can click "%" button to display the values in percentages.

vstephenmsft_2-1726648102625.png

% of Total Workforce = DIVIDE([Perm Headcount],SUMX(ALLSELECTED('Generation'),[Perm Headcount]))

vstephenmsft_3-1726648196935.png

% of Total Turnover = DIVIDE([Turnover Perm Headcount],SUMX(ALLSELECTED(Generation),[Turnover Perm Headcount]))

vstephenmsft_4-1726648285026.png

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @spuri_78 ,

 

I reviewed this post and it seems the problem has not been solved yet.

I reproduced it based on your description.

T Emp table:

Emp TypeGeneration

Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
Perm Gen X
ABC Generation Z
ABC Generation Z
ABC Gen X
ABC Gen X
ABC Gen X

 

Turnover table:

Emp TypeGeneration

Perm Generation Z
Perm Generation Z
Perm Generation Z
Perm Gen X
ABC Generation Z
ABC Generation Z
ABC Gen X
ABC Gen X

 

For the table visual you want, it is suggested to create a dim table below.

Generation table:

Generation

Generation Z
Gen X

Relationships:

vstephenmsft_0-1726648010754.png

Here's what you're getting so far.

vstephenmsft_1-1726648028231.png

Tips: You can click "%" button to display the values in percentages.

vstephenmsft_2-1726648102625.png

% of Total Workforce = DIVIDE([Perm Headcount],SUMX(ALLSELECTED('Generation'),[Perm Headcount]))

vstephenmsft_3-1726648196935.png

% of Total Turnover = DIVIDE([Turnover Perm Headcount],SUMX(ALLSELECTED(Generation),[Turnover Perm Headcount]))

vstephenmsft_4-1726648285026.png

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thank you so much@v-stephen-msft. I didn't have to set up a dim table as I already have relationships set up between the Generations, T Emp, and Turnover tables.

 

I have another issue. I have the table below set up.

spuri_78_1-1726968737203.png

with the following measures:

Single Day = CALCULATE(COUNTROWS(FILTER('Sick_Detailed','Sick_Detailed'[Single/Multiple Days]="Single Day")))
Multiple Days = CALCULATE(COUNTROWS(FILTER('Sick_Detailed','Sick_Detailed'[Single/Multiple Days]="Multiple Days")))
Single Day % = FORMAT(DIVIDE([Single Day],[Total Sick Leave Instances]),"0%")
Multiple Days % = FORMAT(DIVIDE([Multiple Days],[Total Sick Leave Instances]),"0%")

 

I'm trying to create a second table which would look something like this (Count of employees)

image001.png

Sick Detailed table has multiple rows of employees who have taken sick hrs. I guess first, I need to do is to aggregate the hrs by Emp ID and then categorize them in hrs.

 

I have created a Summary Sick Hours Table with the DAX 

SUMMARIZE(Sick_Detailed,'Sick_Detailed'[Employee Id],Sick_Detailed[Org Unit No],"Total Sick Hrs",SUM(Sick_Detailed[Sick Hours]))
 
The problem with the above measure is that it is including employees who have worked in other divisions. I hoped to see one row of employees with total sick hours in the Brisbane region.
 
Any help would be greatly appreciated

 

 

Pavannarne
New Member

Hi Spuri_78,

 

Create Measures for Total Headcount and Total Turnover

Total Headcount = SUM('Table'[Headcount])

Then Total Turn Around
Total Turnover = SUM('Table'[Turnover])

Create Measures for % of Total Workforce and % of Total Turnover:

% of Total Workforce = DIVIDE([Headcount], [Total Headcount], 0)

% of Total Turnover:

% of Total Turnover = DIVIDE([Turnover], [Total Turnover], 0)

 

Formatting the Measures

% of Total Workforce = FORMAT(DIVIDE([Headcount], [Total Headcount], 0), "0.0%")

 

Formatted % of Total Turnover

% of Total Turnover = FORMAT(DIVIDE([Turnover], [Total Turnover], 0), "0.0%")

 

Thanks @Pavannarne I think I'm nearly there. In the Headcount measure, i need to group the headcount (perm staff only) by generations and I think that's the step I'm missing. if I don't the % value for every generation is showing 100%.

@spuri_78 the visual should do the grouping by Generation for you, unless you are trying to do something different? If you put the Generation in the visual, it will automatically use that generation for each row to calculate there Perm headcount, then divide by the grand total headcount. 

 

As per my original reply (I wasn't clear that I was suggesting creating two measures), the % of GT measure: 

 

[% GT Per Headcount] = DIVIDE( [Perm Headcount], [GT Perm Headcount] )

 

[GT Perm Headcount] = CALCULATE ( [Perm Headcount], ALLSELECTED() )

 

will use the Generation filter from the visual to group by the Generation. 

 

If you want to be more specific, you could clear only the filters on Generation, for example: 

[All Generations Perm Headcount] = CALCULATE ( [Perm Headcount], ALLSELECTED( TableName[GenerationColumnName]) )

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

spuri_78
Regular Visitor

@AllisonKennedy 

 

Thanks for your reply. Sorry, I should have been clearer in my intial post. I already have the measure for Total Perm Headcount which is CALCULATE(COUNTROWS(FILTER('T Emp,'T Emp'[Emp Type]="Perm")) and gives the value 117. What I want is the % of 48/117.

 

The individual measure for Perm Headcount for Generation Z is CALCULATE(COUNTROWS('T Emp'),
FILTER ('T Emp','T Emp'[Generations]="Generation Z"),
FILTER('T Emp','T Emp'[Emp Type]="Perm")) which gives 48.

 

Hope this makes sense.

AllisonKennedy
Super User
Super User

@spuri_78 

 

to get the Total you just need to clear ALL filters, you may prefer to use ALLSELECTED instead of ALL: 

 

GT Perm Headcount = CALCULATE ( [Perm Headcount], ALLSELECTED() )

 

% GT Per Headcount = DIVIDE( [Perm Headcount], [GT Perm Headcount] )

 

You can of course combine these two measures into one if you prefer, or keep them separate. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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