Total Employee Count by group

[nleuck_101]

Hello All,

I'm trying to calculate calculate total employee count by group so I can get the % of the Grand Total (ex. level 10 employee count is 100 and total employee count is 350 so the % should be 28.57). Currently though when I try to sum it sums all the numbers (ex. level 10 employee count 100 + 100 + 100 which is 300). This would give me a % of 85.71, this is incorrect.

Any ideas on how to get this to work.

[tamerj1]

Hi @nleuck_101 

Please use 

New Table =
VAR T1 =
    SUMMARIZE ( TableName, TableName[Level], "@Count", MAX ( TableName[Count] ) )
RETURN
    ADDCOLUMNS ( T1, "% Count", DIVIDE ( [@Count], SUMX ( T1, [@Count] ) ) )

[nleuck_101]

@tamerj1 

That only get's me the % of grand total. I still need to display the correct employee count in the visual.

[tamerj1]

@nleuck_101 

The column @Count should disply the correct employee count! 

[nleuck_101]

@tamerj1 

What about showing each level count, the total count (all the level sumed up), and % of grand total by month-year?

[tamerj1]

@nleuck_101 

Tou mentioned that you want to create a calculated table. Seems now you are talking about a measure in a table visual. For a measure the count would be

Count Measure =
VAR T1 =
    SUMMARIZE ( TableName, TableName[Level], "@Count", MAX ( TableName[Count] ) )
RETURN
    SUMX ( T1, [@Count] )

[nleuck_101]

There is still a problem. If I'm only getting the MAX it's not summing the levels that might have different values. Example level 10 could have 100, 100, 100, 90, 70. What I would want is for it to sum 100 + 90 +70 and show level 10 as 260 and not the max of 100.

[tamerj1]

@nleuck_101 
You wanted to sum the distinct values of the count. Fore some reason I missed to read this reply. Please check the two solutions provided below for both a measure and a calculated table.

[tamerj1]

@nleuck_101 
Would like to connect via teams or zoom to look into it as I believe I'm missing something here

[nleuck_101]

@tamerj1 

I need to get the sum of the employee in the level. If you look at the original post level 10 has 3 rows. That's because there were two terminations reasons (I messed up and one of those needs to be a different reason like new employer or etc...). I want to get the total employee count of level 10 which is 100 but Power BI is summing up 100 three times becasue there are three entries and I'm getting 300 which is wrong.

[tamerj1]

@nleuck_101 
And this is a calculated table

Count Measure =
VAR T1 =
    SUMMARIZE (
        TableName,
        TableName[Level],
        "@Count", SUMX ( VALUES ( TableName[Count] ), TableName[Count] )
    )
RETURN
    ADDCOLUMNS ( T1, "% Count", DIVIDE ( [@Count], SUMX ( T1, [@Count] ) ) )

[tamerj1 · Accepted solution]

@nleuck_101 
Please try this measure

Count Measure =
VAR T1 =
    SUMMARIZE (
        TableName,
        TableName[Level],
        "@Count", SUMX ( VALUES ( TableName[Count] ), TableName[Count] )
    )
RETURN
    SUMX ( T1, [@Count] )

[nleuck_101]

@tamerj1 
Thank you! This solution worked as expected.

[nleuck_101]

@sharpie_dog 

It's not that easy. The top part is the desired results but the bottom part it what I'm actually getting.

[sharpie_dog]

What I have seen is if the the "total" is summed up as a calculation in PBI, then you can right click to select the % versus doing a calculation to get the %; if that makes sense.