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.

     

     

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