How find the sum of grouped data?

[elliechasebrown]

I am having trouble creating measures from grouped data.

These company names repeat throughout the dataset I have:

So I grouped them together. (I also have a separate table where I used the "distinct" function to get the exact result of the "group by" but it removed connection to the original table).

What measure/calculated column do I need to use to find the sum of the all the rows associated with each group? 

Example: What measure would I need to use to find that the Chobani sum was 500, using my grouped company names? 

[elliechasebrown]

Thank you for your response!  

I grouped a lot of the "project names" because a lot of them had different spelling but were still apart of the same project. I didn't want to go back into the whole spreadsheet and fix every project name error so that they would all match:

Like all of these Chobani rows are the same project but how would power bi know that if I didn't group them?

I tried creating a measure (sum function) of the number column and applying it but I got an error message:

[elliechasebrown]

For example:

On the left I have UNGROUPED projects. On the right I have them all grouped together. 

When I click on the Chobani group I have on the right, this is what pops up on the left:

How can I get the Chobani group "Total" to actually total? 

I tried creating a SUM measure of the fee totals and it gives me an error message.

[Anonymous · Accepted solution]

Hi,@elliechasebrown 

Regarding the issue you raised, my solution is as follows:

1.First, you can try to check if the data type of your numeric column is numeric instead of text type:

2.Secondly, you can try to add custom columns in powerquery:

3.Then, make sure in the aggregation method is sum:

4.Then use the matrix view,this way you can sort the aggregation by group.:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elliechasebrown]

It was the data type! I changed it to numeric decimal and the SUM of that column finally showed up. THANK YOU!

[PhilipTreacy]

Hi @elliechasebrown 

Download example PBIX file

This calculation is in-built in Power BI.  You don't need to group the data first, all you need to do is add a Table visual then add the Project Name and Value and you should get this.  The default aggregation for the value field is SUM

Regards

Phil