Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YodaJetski
Frequent Visitor

Convert bytes to TB in one query

I'm looking for a way to convert file size in bytes to TB in one query. The source is a large CSV export of a network drive. Which looks like this:

Power BI filesize.png

There are different catagories and for each catagory I want to know the total file size. I used this

 Function to convert bytes into KB MB GB TB PB , et... - Microsoft Power BI Community

to convert the size to GB however the total of some catogories are best shown in TB as you can see beneath.

Total filesize.png

Since the IF function is limited to three I can't make the next step converting gb to tb.

I found this thread but this takes one extra step and another colum.

Solved: Conversion from bytes to gega/tera byte - Microsoft Power BI Community

 

How can I convert from bytes to tb in one query?

 

1 ACCEPTED SOLUTION

Hi @YodaJetski ,

 

I created a new measure to calculate the total based on the selected KB/MB/GM/TB from my original .pbix file.

Total = 
VAR _totalbytes=CALCULATE(SUM('Table'[Bytes]),ALLSELECTED('Table'[KB/MB/GM/TB]))
VAR _KB =
    ROUND ( DIVIDE ( _totalbytes, 1024 ), 2 )
VAR _MB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 2 ) ), 2 )
VAR _GB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 3 ) ), 2 )
VAR _TB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 4 ) ), 2 )
RETURN
    IF (
        _KB >= 1024,
        IF ( _MB >= 1024, IF ( _GB >= 1024, _TB & "TB", _GB & "GB" ), _MB & "MB" ),
        _KB & "KB"
    )

Select only one.

vstephenmsft_0-1665644714821.png

 

Select more than one.

vstephenmsft_1-1665644748727.png

 

 

 

 

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.

 

 

 

 

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @YodaJetski ,

 

You can implement different formats of conversions with one calculated column.

KB/MB/GM/TB =
VAR _KB =
    ROUND ( DIVIDE ( [Bytes], 1024 ), 2 )
VAR _MB =
    ROUND ( DIVIDE ( [Bytes], POWER ( 1024, 2 ) ), 2 )
VAR _GB =
    ROUND ( DIVIDE ( [Bytes], POWER ( 1024, 3 ) ), 2 )
VAR _TB =
    ROUND ( DIVIDE ( [Bytes], POWER ( 1024, 4 ) ), 2 )
RETURN
    IF (
        _KB >= 1024,
        IF ( _MB >= 1024, IF ( _GB >= 1024, _TB & "TB", _GB & "GB" ), _MB & "MB" ),
        _KB & "KB"
    )

 

vstephenmsft_0-1665542023775.png

 

 

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.

Thanks Stephen for explaining and the example. I can see the kb/mb/gb/ AND tb.

 

However I still can't see the total amount. I want to know how many kb/mb/gb/tb all the rows (based on a selection) are together. With this solution I can only see the first, the last value or a count of how many (unique) rows.

 

If I a want to count how many mb/gb/tb the files make together I can't because it is a text field. If I use the measure it counts the total ammount and ads MB/GB etc just not TB.  I want the 1770 Gb shown as 1,77 TB instead

 

KB sample.png

Measure left and calculated colum right

Hi @YodaJetski ,

 

I created a new measure to calculate the total based on the selected KB/MB/GM/TB from my original .pbix file.

Total = 
VAR _totalbytes=CALCULATE(SUM('Table'[Bytes]),ALLSELECTED('Table'[KB/MB/GM/TB]))
VAR _KB =
    ROUND ( DIVIDE ( _totalbytes, 1024 ), 2 )
VAR _MB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 2 ) ), 2 )
VAR _GB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 3 ) ), 2 )
VAR _TB =
    ROUND ( DIVIDE ( _totalbytes, POWER ( 1024, 4 ) ), 2 )
RETURN
    IF (
        _KB >= 1024,
        IF ( _MB >= 1024, IF ( _GB >= 1024, _TB & "TB", _GB & "GB" ), _MB & "MB" ),
        _KB & "KB"
    )

Select only one.

vstephenmsft_0-1665644714821.png

 

Select more than one.

vstephenmsft_1-1665644748727.png

 

 

 

 

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.

 

 

 

 

I had some issues a first. Somehow some bytes where negative values is my table. But after filtering this out it works like a charm. Thank you so much for helping.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.