Forum Discussion

spuri_78's avatar
spuri_78
Regular Visitor
1 year ago
Solved

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   Generation Headcount Turnover Turnover rate of Generation % of Total Workforce ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    spuri_78
    Regular Visitor
    1 year ago

    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