Combining multiple excel and aggregate by category.

[Anonymous]

Good morning,

I have please the following Issue:

I have an excel file with many sheets. some of these sheets have the same format and some common data but for different people.

The data included in this excel file, are the projects where the colleagues work, and the duration.

The aim in the End: to draw a bar chart, that represents every project and the duration of time spent by all of the colleagues.

Like how many hours does the team spend by every project?.

The way I have done it was as follows:

1)- I import only one excel sheet from one colleague.

2)-in Power Query Editor , uder "Applied steps", I click right on "Navigation" and click on "Delete Until End"

3)-Under Data , I click on "Remove other columns", then "expand"

4)-Under Power BI desktop, I added a new column and used the following formula:

SumofHour=SUMX(FILTER(Tabelle,Tabelle[Project]=EARLIER(Tabelle[Project])),Tabelle[Duration])

The Problem Now is: 

1)- Comparing to excel , some value of the sum of hours by project are wrong.

2)- When used Clustered Bar Chart, the value are not the calculated value of "SumofHour", but there are this calculated value * number of rows for each project.

My Questions are please:

1)-Would you please propose for me a better solution for the problem, regardless what I have done?. I mean if you have my task, which steps would you take.

2)- For the Steps I took, how could I please solve the problem of wrong sum (I have read it, where they say , you may try a measure, or add a column). but not solution helped me.

3)-How could I please take only one value of "SumofHour" by project, not sum it up over all the rows, where the project is mentionned.

Thanks a lot.

[v-yalanwu-msft]

Hi， @Anonymous 

I'm so glad you found a solution, Would you mind accept the helpful replies as solutions? Then we are able to close the thread.

Thank you.

Best Regards,
Community Support Team _ Yalan Wu

[Anonymous]

Hey ,

Thanks a lot for answering.  No, the time was kind of hours or hours and a half like(1,1.5,2,2.5....)

I have already found where the problem is.

Thanks a lot.

Best Regards

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Is your duration displayed in PowerBI in the same hh: MM: SS format as in the picture?

If so, it is in text form, so the calculation result is incorrect and count of chart. You could check the Issues forum here:

https://stackoverflow.com/questions/62989953/how-to-find-total-duration-of-time-in-power-bi

https://stackoverflow.com/questions/52054319/show-time-duration-as-hhmmss-in-powerbi-charts

and can you share  a simple data removing sensitive information, along with the results you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.