Forum Discussion
Sum and Order by from table
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
Skilled SharerThat 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.