Sum and Order by from table

[mushi]

Hi,

I have a table that has column called "Employee" and column "hours" in excel table that i have imported into power BI. 

All employees are listed multiple times with an associated value in "hours" column. I would like it in a format (total hours added for associated employee): 

Employee  Total Hours 
A                  1520
B                   800
C                  600


with my end goal being, a want a number of employees that have > 1000 Total hours. e..g 5 employees. 

Thanks

[Rémi]

Hi,

You have just to display a table with your data, the sum will be done automatically, if not, check the format of your column Hours.

Then on the right panel, you can see the filters, do an advanced filter on the hours column : >1000

[mushi]

but i need a number not a graph. That works good for graphs but not for giving whole number in a card. e.g. I want whole number in a card 

[leonardmurphy]

If I'm understanding you correctly, you have 2 fields in your field list on the right hand side of Power BI Desktop: Employee and Hours (see screenshot below). When you drag them onto a visualization (such as a table or card) they show each individual row in the underlying Excel sheet instead of summing up.

There are two options, both have the same result:

1. Once you've created your visualization with Employee and Hours underneath in the 'Fields' list of the visualization, there should be a drop down next to Hours. You can click this and instead of 'Do Not Summarize', select 'Sum'. (In the below screenshot I'm using the card visualization)

2. On the data tab (2nd icon on the far left of Power BI Desktop) or by right-clicking in your field list on the far right, create a new measure called Total Hours, defined as Total Hours = SUM(Sheet1[Hours])  (where Sheet1 is the name of your Excel sheet). You can then use this measure instead of hours in your visualizations.

The 2nd method, though a bit of an extra step, is the better practice. My reasoning:

1. As you get more advanced, your measures will go beyond simple sums, counts & averages that the first option is limited to.

2. With the 2nd method you can give it a specific name ("Total Hours" so it's clear that it's a sum and not, say, an average).

3. The 1st method has to be repeated for every visualization you create whereas the measure created in the 2nd method persists in your model forever more and can be selected again and again. 

4. If you ever want to change the definition of "Total Hours" (e.g. you want to always exclude managers from the measure or you want to subtract a fixed amount of time for lunch breaks each day), you can change the measure in the future and all the visualizations that use it will automatically reflect the change. 

Hope this helps.

[jewelry]

For some of my columns, I do not see this dropdown to change from count to sum. I only see count and distinct count. I tried changing the format of the cells in my excel data sheet to numeric but it did not work.

[leonardmurphy]

Assuming 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.

[mushi]

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.