Forum Discussion
SUM Rows Correctly When Filters Applied
Let's start with the data for demontration:
| id | datasize | action | Custom column mentioned below | category |
| 1 | 9 | create | 3 | category1 |
| 1 | 9 | read | 3 | category1 |
| 2 | 8 | create | 4 | category2 |
| 1 | 9 | read | 3 | category1 |
| 2 | 8 | read | 4 | category2 |
| 3 | 10 | create | 10 | category1 |
The goal is to sum the datasize for all category, but if I simply sum the datasize column, then the same document's size is being summed multiple times, which is obviously not good. Therefore, I came up with the following custom column that divides the datasize by number of rows with the same id:
It shows the size correctly, until I use filters on the action column. For example, if only "read" is selected, then the document (with id=1) sum gets reduced to 6 instead of 9, because the line with "create" action is no longer being summed.
How can I show the datasize correctly every time, when other filters are being applied as well?
Hi,
You do not need a calculated column formula. Try this measure
Min datasize = MIN(Data[Datasize])
Measure = SUMX(SUMMARIZE(VALUES(Data[Id]),Data[Id],"ABCD",[Min datasize]),[ABCD])
Hope this helps.
3 Replies
Hi,
What result are you expecting when Read is selected? There are 2 ID's for Read - 1 and 2. Should the answer be 9+8 = 17?
Please clarify.
Hi Ashish_Mathur ,
Yes, that is correct.
Maybe the I downgraded the probelem way too much. I have an additional category column which I am curious about.
id datasize action Custom column mentioned below category 1 9 create 3 category1 1 9 read 3 category2 2 8 create 4 category2 1 9 read 3 category1 2 8 read 4 category2 so the question I am trying to answear is the following: what is the datasize sum for each category, but obviously each document should be counted only once.
Hi,
You do not need a calculated column formula. Try this measure
Min datasize = MIN(Data[Datasize])
Measure = SUMX(SUMMARIZE(VALUES(Data[Id]),Data[Id],"ABCD",[Min datasize]),[ABCD])
Hope this helps.
