Forum Discussion
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:
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.
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?
Hi Anonymous ,
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.
Select more than one.
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.
4 Replies
Hi Anonymous ,
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" )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
Measure left and calculated colum right
Hi Anonymous ,
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.
Select more than one.
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.