SUM Rows Correctly When Filters Applied

[skutovicsakos]

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:

DocumentUsage[Datasize]  / CALCULATE(COUNTROWS(DocumentUsage), ALLEXCEPT(DocumentUsage, DocumentUsage[DataID]))

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?

[Ashish_Mathur]

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.

[skutovicsakos]

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.

[Ashish_Mathur · Accepted solution]

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.