1 Reply Latest reply on May 28, 2015 7:48 PM by Bill Lyons

# Automatic calculate and view in bps, Kbps, Mbps, Gbps, Tbps

Hey folks I'm new to Tableau and looking for a calculated field that auto selects bps, Kbps, Mbps, Gbps, Tbps based on the median window value. I'm just trying not to reinvent the wheel, in case someone has done this already.

Also I'm looking for any other networking related scripts from others working against networking data.

• ###### 1. Re: Automatic calculate and view in bps, Kbps, Mbps, Gbps, Tbps

Welcome to Tableau Forums! I'm sorry you have not received any response on this sooner.

To build a demonstration, I created a sample data set with random numbers.

Creating a calculated field to determine units by a factor of 1000, 1,000,000, etc., based on the size of the max value in a view is relatively simple. I calculated [maxvalue] as window_max(sum([Value])). Then [Units] is:

if [maxvalue] > 10^9 then 'gbps'

ELSEIF [maxvalue] > 10^6 then 'mbps'

elseif [maxvalue] > 10^3 then 'kbps'

else 'bps'

end

(For simplicity of the example, I assumed powers of 10. If you define these units in powers of 2, you can adjust the Units calculation accordingly.)

Then, I created a calculation to convert the [Value] to [Value in Units]:

case [Units]

when 'gbps' then sum([Value])/(10^9)

when 'mbps' then sum([Value])/(10^6)

when 'kbps' then sum([Value])/(10^3)

else sum([Value])

END

Then, just drag the [Units] to columns to label it, and [Value in Units] to columns so all in the view uses the identified units. Remove the axis label.

As you exclude the large values, you will see the units change from 'gbps' to 'mbps' then 'kbps'.

I hope this helps you and others who may want an automatically adjusting scale.