Using Calculated Fields in Dynamic Tables with Excel

Honestly, I don't consider myself an expert in dynamic tables, although many people think that actually I am this is just because I know many tricks about Excel, however I don't use dynamic tables a lot and therefore there are things about this tables than I don't know.

One day, when I was at work, a partner called me to watch his computer because he had a big doubt about something so he decided to ask me about it. He was managing a huge dynamic table that have a bigger simple table as data source, this dynamic table resumed all sales from region, but the problem was that he needed to show that resume not with the exact amount but rounded to thousands.

At that moment I couldn't help him because I didn't know how to do it, but now I took a time for testing ways of doing that and I want to show you what could have done at that time.

First at all create a simple table with few regions and sales value in 3 currencies.
 
Simple table in Excel

With this information, select it and  go to "Insert" tab and select "Dynamic Table" to create one based in the simple table created above.

Convert dynamic table

You will have your dynamic table created, select  "Regiones" field to have the first column of the new table.

Open the fields list
 
Now select the "Options" tab and inside here select "Fields, Items & Sets" button to select the option "Calculated Field" from the menu.

Select calculated fields

Once you see the new window the next step is to set the new field, first put a name for it and second, place the function for this, in this case you have to use a previous existing field to divide it over 1000 and this result has to be rounded by using the native function round provided by Excel.

Define the calculated field

Doing so will give you a new column in your table that shows the amount of sales but in thousands as it is required.

New column with the calculated field

If you want to see the original value just check the original field from the panel, in this case is the "Dólar" field.
 
See original value

Using this method will provide you many useful ways to present information in an easy way, it depends on the complexity of the functions to be used to calculate the new field.


Comments

Post a Comment

Popular posts from this blog

I Need to Send an Automatic Email with Lotus Notes

Image
If you don't know, Lotus Notes is an email client used in a lot of companies around the world. Therefore, I use it at work everyday, but someday I needed to send an automatic email to a few people all Fridays at 10:00.   To get this task accomplished I decided to create an agent and avoid remembering the fact of sending the email. As you can image, I did this example on a Spanish version of Lotus Notes 8.5 but I will explain it clearly enough. The first step is getting yourself into the inbox, then select the "Create" menu item and inside of it, click on "Agent...". You will get a dialog box opened, in this we have the possibility of setting the rules for this agent. However, in this case we will only set "Trigger" option to "On schedule" with the "Weekly" frequency. Don't forget to push the "Schedule..." button to program the agent in the right way. After this, select "OK" and close the dialog bo...
Read more

Install TWRP Recovery in Xiaomi Redmi 6A

Image
Case: You try to install TWRP Recovery in your Xiaomi Redmi 6A, but the official process doesn't work. Requisites: Xiaomi Redmi 6A phone, USB cable, computer with Windows and TWRP Cactus Tools installed . Steps: Make sure the phone is connected to the computer and in debug mode.  Open TWRP Cactus Tools: Click 'Flash TWRP' and select the right TWRP (be careful with the Android version): Click 'Start Flash' Results: The flash will finish and your phone will have the TWRP recovery.
Read more

Turn Horizontal Visio Swimlanes into Vertical Ones

Image
In one ocassion, a friend at work called me because she had a problem with Microsoft Visio , she was trying to draw a process flowchat but using swimlanes, this was not a problem until she realized that it would be better to use vertical swimlanes instead of horizontal ones.    But, when she tried to drag a vertical one it just put together to the previous swimlanes created before.  To avoid creating a new visio document and lost your work just go to 'Cross-Functional Flowchart' ribbon and select 'Orientation' and 'Vertical'.     And it's done, now you can work with vertical swimlanes or if you like it you can turn them back to horizontal ones.
Read more