DUAL/COMBO AXIS CHARTS

Dual axis = combo axis is the same as dual axis line chart. What you need to understand is that you will encounter times when the measurements you are plotting are using similar scale so it makes sense to synchronize the axis. Or even display ONE axis even though we are still using the same dual axis procedure you see here.

Some times the scale is not related and you need to separate the values in a different chart, or at times even though they are not on similar scale there might still be a correlation between one and the other and it could be beneficial to plot them on the same chart with dual axes. For example in the chart that follows you see even though sales and quantity are not even close to being on the same scale you will obviously see a correlation between the two.

You can even use different mark types, line and bars….

How to create a dual axis chart

  1. Drag the first Dimension to Columns = Month
  2. Drag the first Measure to Rows = Sales
  3. Drag the second Measure to Rows = Profit
  4. Now we have two charts
  5. How do we combine into a dual axis chart?
  6. Click on down arrow of the second measure = Profit
  7. Scroll down to Dual Axis
  8. Done

ANOTHER WAY

  1. Follow same steps as above 1-2
  2. Instead of dragging measure to row drag it all the way to the right axis
  3. Till you see a black dotted line
  4. Drop it
  5. Done

NOTE: Our marks card has now changed. It now show THREE. One for both and one for each chart- Also note that a Measure Name is shown in the All Marks card. This is for all the measures we’ve added and you can edit each one because they are displayed immediately below the marks card as well

ANOTHER WAY

  1. When you drag the second measure to the row you will have two charts
  2. in two separate viz
  3. Click on SHOW ME tab and select Dual Axis/Line Chart
  4. Now both are on the same chart
  5. R&L Axes have been created

SYNCHRONIZE

If you feel that the unit of measures are close enough feel free to synchronize both axes. You can also right click on the right axis and remove it if you feel it’s not necessary since we already synced with the left axis

NOTE: you can only synchronize the second chart NOT the first

  1. Right click on the right axis
  2. Synchronize
  3. You can still click on each line and format further

COMBINED AXIS

What if we want to view three different measures against the same axis. Combined axis charts combine multiple measures that share the same axis here you see one chart with separate marks. We will also create one with stacked marks. The initial processes are very similar and varies at the end when we stack them.

 

 

Separate Marks

Lets say we want to analyze our spending across our product types, where all the measures are measured in dollars. We will use COGS, Total Expenses, Marketing…. for now across Product Type

  1. Drag Marketing to Rows
  2. Drag Product Types to Columns
  3. Now we have the cost of marketing across our product types
  4. Now instead of dragging COGS to the Row or to the right axis as we did in the dual axis section before
  5. We drag it to the LEFT AXIS
  6. Two Green Parallel Boxes appear the boxes mean they will share the axis
  7. Drop it there and now you see we have a combined axis for both Marketing and COGS
  8. DO the same for Total Expenses
  9. Now we have 3 measures sharing the same axisNOTE: Measure Values and Measure Names are displayed in the Filters pane and in the viz. This happens when you bring in multiple measures into the view and they share the same space. T invokes the Measure Values & Names are automatically invoked. Measure names contain the name of the fields while Measure Values contain the values for each measures.

If you open the Measure Names in the Filter Pane you’ll see all the values in the Measure Name, you can actually replace them here if you want or add or delete any of them.

SEPARATE COLORS/MARKS

Since we can use Measure Values and Measure Names as we can with any other dimension or measure, this means we can move Measure NAMES to Color Marks this way we will have each Measure Name colored differently.

So now we not only have a label and a bar for each measure we also have separate colors

Stacked Marks

From the steps above all we have to do is at the completion of the above steps is:

  1. Remove Measure Names off the Columns or just drag it off
  2. The view will now display the measure Values stacked marks
  3. You can change the order of the stacked bars by moving each field in the Measure Values
  4. Or by moving the order in the Measure Names / Color Legends

MEASURED VALUES CHART

We already covered measured values before – These are generated automatically by T when we connect to a data source. Now one of the ways we can make use of the convenience is to chart multiple measurements on the same chart. So here I will show an example of how to create a dual axis chart as we did in the above sections but using the Measure Values and Measure Names instead.

  1. Let’s say we want to plot Profit, Sales, Discount on the same chart
  2. I used those 3 because they are generated for the data set I am using and are based on dollars
  3. I can drag all 3 Profit, Discount, Sales to R – Order Date to C
  4. I can use dual axis on the first 2 charts to combine them
  5. I’ll end up with two charts but I want all 3 on the same chart?
  6. Order Date to C
  7. Measure Values to R
  8. Now you see a crazy looking line chart
  9. Remember we need to filter out some of the measured names
  10. so Drag Measure Names to FILTERS pane
  11. Click on the down arrow
  12. Uncheck the measured values we don’t want and leave Discount, Profit, Sales
  13. Now the chart will change and the Measure Values listed on the SHELF will change
  14. It is hard to tell which one is which because it is connecting all the values with ONE LINE
  15. Hover the mouse over the line and you will see all 3 values are there
  16. DRAG Measure Names to COLOR
  17. Now you see all three lines on the same chart with one axis
  18. If you want DRAG Measure Names to Label or just look at the right side to know which color is for which measure

SCATTER PLOTS

Investigate relationships between quantitative values. For a large number of continuous information which can take unlimited number of answers when compared to another variable, then scatter plots are powerful, because they can show the distribution of the outcome and are typically used in statistics and forecasting. If you are looking for a correlation between the two.

  1. For Scattered plots you need to measures so we can plot on two axis
  2. Take category off the sheet
  3. Add discount
  4. SO we are plotting discount vs profit
  5. Now only one circle shows up
  6. Go to ANALYSIS TAB/Uncheck Aggregated Measures
  7. Because we want to see every single point of discount that’s why we don’t want aggregated

MAPPING

What is geographic data? Geographic data is data that describes a physical location. Is comprised of a Latitude and Longitude coordinates. T has two types of maps, symbol and filled maps.

T has an internal engine that recognizes 8 different geographic data plus Latitude and Longitude. So T will automatically assign coordinates to any of the data listed above, it’s called Geo Coding. Geo coded fields will have the globe icon next to them.

SYMBOL MAPS

So with a symbol map we are basically plotting a scatter plot on an x & y axis, that’s it. It just happens to have a global map in the background that corresponds to the coordinates in the data.

Geographic Role
  1. If the data contains states, regions, cities, countries…. and we drag the field onto the worksheet T will automatically create a map with the fields in it
  2. Also T will automatically generate L&L pills in rows and columns
  3. Symbols are all the same size and centered in each state
  4. In case you are not sure what is available in your data set with respect to Geographic data
  5. Click on Data Source
  6. Find any of the columns with a GLOBE icon (this means it is geographic data)
  7. Click on the GLOBE
  8. Hover down to Geographic Role
  9. It will list all the available fields in the data set that will provide geographic data like: are code, CBSA, city, congressional district, country region, state/province,zip, latitude and longitude and it also allows us to create from…..

DATA & MAP LAYERS

Layers example
  1. Drag State to Worksheet – a map is created
  2. Either use the SHOW ME or the dropdown in Marks panel and choose FILLED MAP
  3. If you drag Discount to the map now we get NOTHING – well not exactly. We get discount added and if you hover over the map you will see the value of DISCOUNT for each state
  4. If we want the discount to show as filled circles change map type to filled circles
  5. Click on the map background
  6. On the main ribbon click on MAP
  7. Then MAP LAYER/A whole new panel appears on the left
  8. Choose whatever layer you feel will enhance the viz
  9. If a layer is not available at the zoom level you are at change your zoom level until that layer becomes available and then you can select it
  10. DATA LAYER is another option we can make use of
  11. Click on the down arrow and choose Population for example in case you are comparing sales per state this will give you an idea of how populous a state, city, county is if you choose to use this data to color the background

CUSTOM GEO CODING

  1. Sometimes T doesn’t recognize, or data is incomplete or maybe have an error
  2. Drag CITY onto the screen and you will see in the bottom right corner
  3. 405 UNKNOWN
  4. sometimes there is a misspelling, or maybe an abbreviation was used and not caught when cleaning the data
  5. Click on the UNKNOWN  notice in the lower right corner
  6. it will ask you if you want to FILTER DATA=exclude them
  7. SHOW DEFAULT which will show a 0 if null we don’t want to do that, let’s look at them and see if we can edit them so choose
  8. EDIT LOCATIONS/DATA:
  9. A new windows will open with a list of cities we notice in this case they all seem to be ambiguous-
  10. If you recognize the issue and there aren’t too many you can fix them one by one:
  11. Click on one of the rows in the left column
  12. Click on the dropdown arrow on the right and try typing in the city name, or if you know the latitude and longitude
  13. in this case I don’t and when I try typing in the city name T doesn’t recognize it
  14. It is possible that it is not recognizing the state
  15. UP in the upper section of the window click on STATE arrow
  16. FROM: fixed do not choose that one because they don’t all belong to the same State
  17. NONE doesn’t apply
  18. FROM FIELD will instruct T to look in the data set for the state and try to match them
  19. and sure enough they all become grey and find a match location
  20. No UNKNOWNS anymore
Example
  1. BASIC 1
  2. Click on Sheet name down on the bottom ribbon
  3. If our data contains Country Code, all we have to do is DRAG that field onto our canvas and a map will appear with the countries that exist in our data already plotted.
  4. Also notice that Longitude appears in COLUMNS
  5. Latitude appears in ROWS
  6. Now you can drag any measures onto the map and you will see that data reflected when you hover over a country
  7. Now you see SUM CO2 is displayed on the Marks Panel
  8. If we wanted to change the Size of the mark we can do it here
  9. You can easily switch between showing the countries as a circle to coloring the entire country by opening the SHOW ME panel on the right side
  10. Click on the OTHER world map chart
Example
  1. Instead of dragging let’s open a new sheet
  2. Now DOUBLE CLICK on CO2 per Capita since it is a MEASURE (see measures later) it will automatically appear in ROWS
  3. Now we want to see over the years, so again DOUBLE CLICK on Year and since it is a DIMENSION it will automatically appear in COLUMNS
  4. This obviously shows the SUM of all countries, so for the whole world we see the trend
  5. If we add COUNTY to the chart it will break it down per country

DUAL LAYER MAPPING

This is a combination of Using dual axis and Mapping layer in a sense. What if we want the map to color the regions and show sales and profit in each city.

Colored Cities
  1. This first example colors the cities based on the region they are in
  2. Drag state
  3. Drag Region to Color
  4. Now we have a map with colored regions
  5. Drag Sales to sheet and now we have sales showing up in the tooltip as the example before
  6. We can change the type of chart from map to shape and we get the sales on the map with each regional sales colored according to the region they are in but that’s not what we want
  7. We want the states colored by region and the cities showing their own shapes, so we have to do a DUAL AXIS chart in a way or DUAL LAYER
Colored States & More
  1. So let’s go back to the previous example and stop at step 4
  2. So we have a colored map by region

DUPLICATE A MAP

  1. We want to duplicate the map that we have
  2. click on the Latitude in the Row
  3. CTRL and drag to the right to create a duplicate
  4. The duplicate will appear below
  5. At this time they are independent so click on the bottom map
  6. Drag City in
  7. Now Drag Profit to Color shelf
  8. Drag Sales to Size
  9. You will notice that the circles are there for each city but they are all the same size
  10. REASON is the chart is still FILLED MAP and we need to change it to circle or shape
  11. Now you see the Sales and Profit by city on the map

COMBINE

  1. COMBINE the two is the next step
  2. If you click on the drop down for the LEFT MOST PILL in the row you don’t find the option
  3. If you click on the drop down for the RIGHT MOST PILL in the row
  4. DUAL AXIS
  5. Now you have both sales and profit for each state by region

CROSSTABS – DATA TABLES

Not highly visual but can be very helpful within a dashboard, to drill down to more details as a reference.

Just in case you need to review the data you are working with

  1. Upper right corner of the fields pane
  2. Click on the data table icon
  3. in the popup you’ll see the data you are working with
  4. feel free to drag columns next to one another if you are looking for something specific
  5. Any changes you make here will not be reflected or saved in the data
Example

Let’s say I want to compare Typical Education of Actual Employee in 2014 to Typical Education of Project Employees in 2022

  1. Drag Actual Employees 2014 to the view
  2. You will see Sum(2014…) is displayed in the Marks
  3. Drag Typical Education to the Rows
  4. Now to compare 2022 just drag 2022 to the right of 2014 column in the view and now you the two columns side by side
  5. So you see the new measure is added in the column section which include both measures now
  6. Here you can drag Measure Names to the row and you see how the table changes
  7. So what we’re asking T that for each level of education we want to see the compared values for both years
  8. Or I can switch the placement of the Pills in the Row and now
  9. T is showing us the Typical Education level for each year broken down by year not by level of education

FORMAT GRIDLINES

To format gridlines just

  1. Right click on any cell in the table
  2. Format
  3. Choose Rows and or Column
  4. Format

TOTALS & AGGREGATIONS

You will undoubtedly want to add Totals and Sub-Totals to the table and we can do that in two different ways:

Use Analysis from Top Ribbon
  1. Click Analysis
  2. Choose Totals
  3. Show Row Grand Totals
  4. Show Column Grand Totals
  5. Show Sub Totals
  6. Right Click the heading Total/SubTotal… and Format>Label change the Heading from Total to BLAH
Use Analytics Tab in Fields Pane
  1. Analytics Tab
  2. Drag Totals
  3. As soon as you drag it out you will be provided with a window that allows you to choose the same options as you have above

HIGHLIGHT TABLE

This is pretty much a Cross Tabs/Data Table where each cell is colored according to a second dimension we are trying to analyze. We’ll see later how we can also use Heat Maps for this as well if we wanted to avoid using numbers all together. If the user is looking for a CrossTab then using a Highlight Table will clarify it even more for them, because this way they get to see the numbers and color will make it easier for them to find the answer they are seeking.

Highlight Tables use colors to emphasize outliers and trends. Encodes a measure on Text and Color. Also make sure you set Shape to Square so it covers the cell. If you leave it at automatic you’ll end up with a colored text

  1. Drag Sales to Text – now you have one cell with the Sum of Sales
  2. Drag Sales to Color
  3. Set shape in the dropdown that says Automatic > Square
  4. This will color the entire cell instead of the text only
  5. Drag Category to Rows
  6. Drag Sub-Category to Rows
  7. Drag Region to Column
  8. Go into the Color Marks and change to diverging orange to blue

Note you can do this another way of course

  1. Drag step 5 & 6 & 7
  2. Drag Sales to the table
  3. Drag Sales to Color
  4. Change shape to Square
  5. Go into Color and change color

NOTE: It is very important to adjust the color and center it strategically to show the data we want to show.

For example, If we have a series of test scores, once you open the Edit Colors palette it will show you the minimum and maximum test scores. What if we want to focus on how many scores were above 75% which we target as the C grade – then we can center the white color to be the value we are focused on, so we’ll have a clear definition of what’s above and what’s below.

 

And we end up with below. Can you see which quiz the students struggle with?

HEAT MAPS

Heat maps are used when you are using multiple measures  and multiple dimensions. Of course I will show later how you can use bar charts instead of heat maps. So you can highlight size and color.

Let’s say you want to see Sales and Profits for each Category from each Department in Every Region.

  1. Drag Dimension 1 and 2 to Columns= Region & SubRegion
  2. Drag Dimensions 3 & 4 Department and Category to Rows
  3. Drag Measure Sales to Size>change it to square
  4. Drag Measure Profit to Color>set Orange to Blue Divergence
  5. If you wish to add filter like Profit and display so the user can filter down by profit range
  6. You can also use the Show Me to build it if you want

PIE CHARTS

Pie Charts have a bad wrap because most developers use too many groups in it.

  • It is not meant for comparisons.
  • Stay closer to 2 than 5 categories

CTRL + Select to select more than one data field – this way you can select the fields then select SHOW me to create a pie chart

CTRL + Select Pill from Marks pane to drag the same PILL to the view. It’s like copying

TREE MAPS

When we have hierarchical data with more than 3-4 categories, and we wish the show the relationship to each other as a whole, use Tree Maps

It uses nested rectangles to show a relationship between dimensions. Tree Maps only use the Marks Cards and does not use Columns and Rows. So drag 2 of your measures to

  1. Drag Dimension to Color – Region
  2. Drag Measure to Size – Sales
  3. Drag Dimension to Detail – Category
  4. Add the same dimension & measure (category & sales) to Label so it clarifies the data
  5. All label information is included in the tooltip as you know by now
  6. Smallest marks will not have labels because the boxes are too small
  7. Drag the same Region-Category-Sales to Label if you want
Add Regions

If you add regions to the tree map then the region with the biggest contribution will be displayed in the upper left corner. The country that has the largest GDP in this case will be in the upper left of the Region as well.

Add Another Color/Region

If you want to add another Dimension, just drag the dimension to the Detail mark.

  1. Click on the new dimension just below the Marks Panel and switch it to color

WORD CLOUD

Is just like a tree map usually measure frequent contribution. With T you can add Size to any measure not just the number of records.

A word cloud could be built from a tree map by changing the mark type to Text

  1. Just like a tree map the stretcher of the words are controlled by the measures and dimensions in your view.
  2. The words you see are controlled by the text on Label

BUBBLE CHART

  1. Another chart that show relative values without axes.
  2. A tree map can be converted to Bubble chart by changing the Shape to Circle

ADD DIMENSION TO ROW

You can add another dimension to a tree map and that separates the data into rows with each row being a Tree Map

REFERENCE LINES & BANDS

We might need to highlight an axis line, or a range/band as a reference line. We can set the line against a dimension/pane or entire table.

How to Add Reference Lines

Option 1

  1. Click on the left axis – because reference line will go against Y axis (in this case)
  2. A popup window appears from which as you can see above you will be able to choose the other options aside from reference lines
  3. Let’s choose Reference Line for now
  4. Line- Value from the dropdown you choose the Measure you want to plot against – lets say we want to use a specific value from Sales
  5. IF Sales is not an option then
  6. Drag Sales onto Detail and now it should appear as an option
  7. Decide if you want Entire Table, Per Pane, or Per Cell
  8. Choose Average/Sum…. whatever you wish
  9. If you want to use a fixed line then enter the value in the Value box
  10. Label the line as you want to appear , if you want you can choose Computation if you are not using a fixed line
  11. Now if you choose more than one field in the chart then the reference line will automatically recalculate

Option 2

  1. Analytics Pane
  2. Reference Line
  3. Same box opens up as above

BANDS

Very similar to reference Line as to how you set it up. Bands display certain data that falls within a certain value/range. You will see above a dynamic range for each pane. You can also have it setup as a fixed band.

DISTRIBUTION

Referenced distribution is a variation of bands but shows a distribution based on a measure but you can base the referenced shading by

  1. Confidence Interval
  2. Percentage
  3. Percentiles
  4. Quantities
  5. Standard Deviation

You can fill in the coloring above, below, or symetric

BOX PLOTS

A combination of reference lines and distribution bands

NOT REVIEWED

ANALYTICS

  1. Left Panel, Data Tab or Analytics
  2. Choose whichever line is shown to use or later we will add lines as we learn later

TREND LINE

  1. Pick Trend Line
  2. Choose

SYNCHRONIZE

EXAMPLE

  1. After we created the upper and lower bounds for the STD Dev
  2. Drag one after the other to the right side
  3. Now you see a dual axis appearing
  4. Now you notice that the right and left axis are not in SYNC
  5. Right click on the right axis
  6. Edit Axis
  7. Synchronize dual axes
  8. Now you see the Boundary lines are in place