Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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.
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.
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:
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
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]) )
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
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.
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.
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
User | Count |
---|---|
62 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |