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

 

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.

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.

     

     

7 Replies

  • Pavannarne's avatar
    Pavannarne
    New Member
    1 year ago

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

      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's avatar
        AllisonKennedy
        Community Champion
        1 year ago

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

         

  • 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

       

       

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    1 year ago

    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. 

  • spuri_78's avatar
    spuri_78
    Regular Visitor
    1 year ago

    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.