I have a report displaying file size against users - how can I accurately display this?

[dinosainsburys]

I have a report which displays files and folders and their file size. For example,

Path	Size	Owner
C:\User\Tom	12.5GB	Tom
C:\Users\Tom\Documents	3GB	Tom
C:\Users\Tom\Pictures	2GB	Tom
C:\Users\Tom\Music	1GB	Tom
C:\Generic	11.5GB	Tom

If I 'SUM' all the values I get a value much larger than intended and therefore incorrect. Documents, Pictures, Music would ultimately make up the size for C:\Users\Tom. But of course, the 'SUM' function would add all of it. If I do 'MAX' it will only take into account the C:\Users\Tom value.

Is there a way to only get the top-level folders and their size?

[Anonymous · Accepted solution]

Hi @dinosainsburys ,

Thanks for the reply from rajendraongole1 .

Do you want to add the sizes of C:\Users\Tom\Documents, C:\Users\Tom\Pictures and C:\Users\Tom\Music? Is the size of your C:\User\Tom 12.5GB the original data? So 2+3+1 is not equal to 12.5? What are top-level folders? Does it mean C:\Generic or C:\User\Tom?

First, create a calculated column to calculate the number of /:

\count =
LEN([Path]) - LEN(SUBSTITUTE([Path], "\", ""))

Second, create a calculated column to extract the Size from your original data. Because formats like 3GB are of String type and cannot be summed.

SizeNum =
FORMAT(LEFT(
     'Table'[Size],
     VALUE(
         LEN('Table'[Size]))-2),"Fixed")

Finally, create a measure to calculate the sum of Documents, Pictures, and Music.

SizeMeasure =
VAR _size = CALCULATE(SUM('Table'[SizeNum]),'Table'[\count] = 3)
RETURN IF(MAX('Table'[\count]) = 3,_size & "GB",BLANK())

The page effect is as follows:

I set C:\Generic as top-level folders, that is, set Filter to 1 on the visual object.

The page effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[rajendraongole1]

Hi @dinosainsburys -Instead of manipulating strings directly in DAX is difficult, you can handle this in Power Query (M Language) to preprocess your data:

Add a Custom Column
IsTopLevelFolder = Text.Contains([Path], "\") = false

apply with filter rows where IsTopLevelFolder is true.
Close & Apply:

Apply the changes and load the data back into Power BI and then create a Measure for Sum the sizes of the filtered data.
DAX
TopLevelFolderSizeSum = SUM(Files[Size])

Check the above approach and let me know.