Forum Discussion
Function to convert bytes into KB MB GB TB PB , etc
Eman,
If you are using M query, try this :
if [Used Space] < 1024 then Text.Combine({Number.ToText([Used Space]), "B"}, " ") else
if [Used Space] < 1048576 then Text.Combine({Number.ToText(Number.Round([Used Space]/1024, 2)), "KB"}, " ") else
if [Used Space] < 1073741824 then Text.Combine({Number.ToText(Number.Round([Used Space]/1048576, 2)), "MB"}, " ")
else Text.Combine({Number.ToText(Number.Round([Used Space]/1073741824, 2)), "GB"}, " ")If using DAX (Preferred way), create a measure like this:
Total Used Space =
VAR total =
SUM ( Table1[Used Space] ) + 0
RETURN
IF (
total < 1024,
FORMAT ( total, "#0.0# B" ),
IF (
total < POWER ( 2, 20 ),
FORMAT ( total / POWER ( 2, 10 ), "#0.0# KB" ),
IF (
total < POWER ( 2, 30 ),
FORMAT ( total / POWER ( 2, 20 ), "#0.0# MB" ),
FORMAT ( total / POWER ( 2, 30 ), "#0.0# GB" )
)
)
)Good stuff, however I am understandably getting an alphabetical sort instead of numerical sorting. Anyone know how I can apply custom sorting to this measure so that the sorting uses the original numeric value (i.e. Table1[Used Space])?
Thanks.
I saw this article using REPT(UNICHAR(8203) and it worked for me.
https://powerbi.tips/2019/09/custom-sort-order-within-a-dax-measure/
My measure got like this:IF(_Result = "TB", REPT(UNICHAR(8203),1) & FORMAT ( _TotalRAM /1024, "#.0# TB" ),IF(_Result = "GB" && _TotalRAM > 100, REPT(UNICHAR(8203),2) & FORMAT ( _TotalRAM, "#0.0# GB" ),IF(_Result = "GB" && _TotalRAM > 10, REPT(UNICHAR(8203),3) & FORMAT ( _TotalRAM, "#0.0# GB" ),IF(_Result = "GB" && _TotalRAM > 0, REPT(UNICHAR(8203),4) & FORMAT ( _TotalRAM, "#0.0# GB" ),0))))
The limitation is that we can only use it on a table or matrix.Here's an implementation of the sorting solution, for use in a DAX measure.
Active = VAR total = sum([ACTIVE_BYTES]) VAR maxlen = 20 //set maxlen larger than the possible number of values to the left of the decimal point RETURN IF ( total < 1024, REPT(UNICHAR(8203),maxlen-LEN(TRUNC(FORMAT ( total, "#0.0#" ),0))) & FORMAT ( total, "#0.0# B" ), IF ( total < POWER ( 2, 20 ), REPT(UNICHAR(8203),maxlen-(3+LEN(TRUNC(FORMAT ( total / POWER ( 2, 10 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 10 ), "#0.0# KB" ), IF ( total < POWER ( 2, 30 ), REPT(UNICHAR(8203),maxlen-(6+LEN(TRUNC(FORMAT ( total / POWER ( 2, 20 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 20 ), "#0.0# MB" ), IF ( total < POWER ( 2, 40), REPT(UNICHAR(8203),maxlen-(9+LEN(TRUNC(FORMAT ( total / POWER ( 2, 30 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 30 ), "#0.0# GB" ), REPT(UNICHAR(8203),maxlen-(12+LEN(TRUNC(FORMAT ( total / POWER ( 2, 40 ), "#0.0#" ),0)))) & FORMAT ( total / POWER ( 2, 40 ), "#0.0# TB" ) ) ) ) )
Anonymous,
There's something off about both of those, but I want to do this the right way, so let's go forward with DAX.
I added my m query and some sample data to my original post. The size values are in E-L in the original data. They're listed there in bytes, so I'm going to need to come up with a way to get bigger in size.
Dax as a custom column returned the same data for all of the rows.
Sweet, Nick. Worked like a champ.
If you want to use the M query for positive and negative values e.g. for changes in folder sizes make sure to replace '[Used Space] <' with 'Number.Abs([Used Space]) <'
How can i modify this for if i am starting with KB instead of B?
This works for me using M Query version but for some strange reason I am getting 4 trailing 0's at the end and cannot figure out why?
Like
whole number : 812643 becomes
793.600000 KB
Can anyone tell me why this is happening?
