Forum Discussion
Sum and Order by from table
Skilled SharerAssuming I persuaded you on the 2nd method, you can then add a 2nd measure:
Employee Count = DISTINCTCOUNT(Sheet1[Employee])
to get a count of employees.
And to filter to employees with more than 1000 hours, select your Total Hours measure under 'Visual Level Filters' choose 'Is Greater Than' and type in 1000. Since it is a measure, you can only filter the visualization.
Hey,
Thanks so much for your help, but i think you slightly understood me. So I have column employee name and hours (sum column). If i display these i can get a tabe like such:
employee name hours
a 5876
b 876
c 1075
d 688
Then applying a filter i can get ones over 1000. This lists employees who worked more than 1000 hours. BUt what i would like is a number e..g "8" employees worked over 1000 hours. Not a list of who?
Does this make sense,
Again thanks so much for your help
- leonardmurphy
That gets a little more complicated so I don't guarantee this is the most performant way of doing this, but try:
Employee Count Over 1000 Hrs = CALCULATE(DISTINCTCOUNT(Sheet1[Employee]), FILTER(SUMMARIZE(Sheet1, Sheet1[Employee], "Total Hours", [Total Hours]), [Total Hours] > 1000))
This depends on the Total Hours measure I mentioned above: Total Hours = SUM(Sheet1[Hours]) ...where Sheet1 is the name of your Excel sheet.
This is broken into 3 parts.
1. SUMMARIZE(Sheet1, Sheet1[Employee], "Total Hours", [Total Hours]). This is like doing a GROUP BY in SQL. It's saying group the table Sheet1 by Employee and include a column called "Total Hours" that represents the pre-existing [Total Hours] measure. I could also do "Total Hours", SUM(Sheet1, [Hours]) but then if I change my definition of Total Hours, I'd have to change it in 2 places. The effective result is the table visualization I talked about above before it was filtered.
2. FILTER(SUMMARIZE...), [Total Hours] > 1000). This is like the HAVING clause in SQL. It says only return rows (employees) from the SUMMARIZED table that have more than 1000 hours.
3. CALCULATE(DISTINCTCOUNT(Sheet1[Employee]), FILTER...). This is saying do a DISTINCTCOUNT of the employee column in the FILTERED version of the table from step 2.