Stuck on Divide Measure total

[spuri_78]

Hi guys,

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

Generation	Headcount	Turnover	Turnover rate of Generation	% of Total Workforce	% of Total Turnover
Generation Z	48	3	6.3
Gen X	12	1	8.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.

[Anonymous · Accepted solution]

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:

Here's what you're getting so far.

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

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

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

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.

[spuri_78]

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.

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)

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]

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

[spuri_78]

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

[AllisonKennedy]

@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]) )

[spuri_78]

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

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