Forum Discussion
Issues with SUM Grouping
Hi,
I've used PowerBI quite a bit previously but am building a new report today and I'm getting some behaviour I've not experienced before with totals.
In my table I just want to show the value of each line so I've disabled SUM of the value column and then my chart and cards should be the SUM of all values in the Type category and only those with a status of 0. This is just done by setting filters on the card but I seem to be getting the sum of all values rather than just the filtered ones.
To try and explain this further this is an example of my data:
| ID | Type | Value | Status |
| 1 | Type 1 | 100 | 1 |
| 2 | Type 1 | 100 | 0 |
| 3 | Type 2 | 100 | 1 |
| 4 | Type 2 | 100 | 0 |
| 5 | Type 2 | 100 | 0 |
My card is then set to show all Type 1's with a status of 0 so it should show 100 but it's showing 200 instead and my card for Type 2 which should show 200 is showing 300.
It's the same on my table if I enable SUM on the value column it will show similar to the below:
| ID | Type | Value | Status |
| 2 | Type 1 | 100 | 200 |
| 4 | Type 2 | 100 | 300 |
| 5 | Type 2 | 100 | 300 |
I'm fairly sure I'm not doing anything differently to what I usually do and it usually just works. I do have a relationship between two tables as the ID and Status come from one table and the type and value come from another table if that would make a difference.
I'm working on redacting some of the data so that I can share screenshots if needed but thought I'd query with this initial explanation in case anything stands out.
Appologies for the delay in coming back to you. I hadn't been able to work on this again for the last week.
These measures helped for one of the cards but not the other 2 and I've been trying to work out what the difference is. I've made some queries directly in SQL to compare the row data and in SQL it matches my table in Power BI but not the card values.
Seen as I had created the queries in SQL anyway and I know SQL better than I do PowerBI I just created a view with what I needed and used those in PowerBI to get what I needed for now.
3 Replies
parkinson13 , Check the three measures if they can help
calculate(sum(Table[Value]), filter(Table, Table[Status] =0))
calculate(sum(Table[Value]), filter(Table, Table[Status] =0 && Table[Type] = "Type 1"))
calculate(sum(Table[Value]), filter(Table, Table[Status] =0 && Table[Type] = "Type 2"))
Appologies for the delay in coming back to you. I hadn't been able to work on this again for the last week.
These measures helped for one of the cards but not the other 2 and I've been trying to work out what the difference is. I've made some queries directly in SQL to compare the row data and in SQL it matches my table in Power BI but not the card values.
Seen as I had created the queries in SQL anyway and I know SQL better than I do PowerBI I just created a view with what I needed and used those in PowerBI to get what I needed for now.
Hi parkinson13 ,
Which field are you connecting the two tables through?
Looking forward to your screenshots and more detailed explanation.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
