Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] )
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] ) ) )
That only get's me the % of grand total. I still need to display the correct employee count in the visual.
What about showing each level count, the total count (all the level sumed up), and % of grand total by month-year?
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] )
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.
@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.
@nleuck_101
Would like to connect via teams or zoom to look into it as I believe I'm missing something here
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.
@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] ) ) )
@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] )
It's not that easy. The top part is the desired results but the bottom part it what I'm actually getting.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |