How i can show the Sum of Hours for a field for each User

[mvcsharepointde]

I have create a Data source from an API , as follow:-

the Data i am showing is as follow:-

with these fields; Name, LogStart, LogEnd, Type & TotalTime... now how i can show the Sum for the TotalTime field for each Name? also this should work for filtered data? Can anyone advice?

Thanks

[cath1ynn · Accepted solution]

Start with converting your TotalTime field to Decimal number data type with Summarization of Sum, then you can add a Matrix visual with the Name field in the Rows and the TotalTime in the Values.

[mvcsharepointde]

@cath1ynn  ok worked well, thanks for the great help

[mvcsharepointde]

@cath1ynnthanks fo the reply, i did the steps you mentioned and i convert the total time to decimal.. but how i can show the total sum for the TotalTime for each name? i can not see where are those steps in you reply. are you able to advice?

Thanks in advance for your help.

Also when i click on the TotalTime field i can not see any option to summarize based on the sum for each name??

[cath1ynn]

Looks like the field you have selected in that screenshot is not a number field, hence why you only see those few aggregation options. 

[mvcsharepointde]

@cath1ynnthanks a lot for the useful reply. as i am a bit new to  Power BI, can you provide some screen shots of what i need to do exactly ? Thanks

[cath1ynn · Accepted solution]

I created a temp Excel workbook with those same columns that you have and imported it into Power BI using Power Query. By default, Power Query has imported all my columns as "Any" data type. Which mean it can contain a mix of letters and numbers.

What you want to do is select the TotalTime column and then go to the Data Type menu at the top and select one of the Decimal number options depending on your needs

Here's what Decimal Number looks like:

And here's what Fixed decimal number looks like:

With Fixed decimal number, you can set it to have an exact number of decimal, so if your data has more digits than what you've set the decimal numbers to be, it will simply be rounded to that number of decimal places.

Of course, you can also do this in Power BI Desktop.

Here you can see my table looks the same as you.

If I then select the TotalTime field in the Data pane, Power BI will show the Column tools for that field where you can see that the Data Type is set to Text.

Just change the Data type to Decimal number or Fixed decimal number. You will get a warning like below. Just click Yes.

And your data table should now look like this:

There are pros and cons to making this change in Power Query vs. in the Power BI Desktop model view. I've listed them below.

Changing Data Type in Power Query

Pros:

1. Early Transformation: Data type changes are applied early in the data transformation process, ensuring that all subsequent steps in Power Query work with the correct data type.
2. Consistency: Data type changes in Power Query ensure consistency across different steps and transformations.
3. Error Handling: Errors due to incorrect data types can be caught and resolved early, reducing the risk of issues later in the modeling phase.
4. Performance: For large datasets, applying data type changes in Power Query can improve performance, as it might reduce the data size (e.g., converting a text column to a date).

Cons:

1. Initial Load Time: Data type changes in Power Query might increase the initial load time, as the data transformation needs to be applied during data refresh.
2. Complexity: For users unfamiliar with Power Query, changing data types might add complexity to the data preparation process.

Changing Data Type in Power BI Desktop Model View

Pros:

1. Simplicity: Changing data types in the model view is straightforward and can be more intuitive, especially for users less familiar with Power Query.
2. Flexibility: Easy to make quick adjustments to data types without going back to the Power Query editor.
3. Post-Load Transformation: Changes are applied after the data is loaded, which can be beneficial for quick testing and adjustments.

Cons:

1. Delayed Error Detection: Errors related to incorrect data types might only become apparent later in the modeling or visualization phase.
2. Performance: Applying data type changes in the model view might not optimize performance as efficiently as changes in Power Query, especially for large datasets.
3. Inconsistency: Subsequent transformations or calculations might be affected if data types are not correctly handled early in the process.

Recommendation

Best Practice: Change Data Types in Power Query

For most scenarios, it’s generally better to change data types in Power Query because:

• It ensures data type consistency throughout the entire data transformation process.
• Early error detection and handling improve the reliability of your data model.
• Performance optimization can be achieved by reducing the dataset size early.

However, for quick fixes or adjustments, changing data types in the model view can be more convenient and efficient.

[cath1ynn · Accepted solution]

Start with converting your TotalTime field to Decimal number data type with Summarization of Sum, then you can add a Matrix visual with the Name field in the Rows and the TotalTime in the Values.