In this recipe, we will explore how to display different dimensions in the same graph using a parameter and calculated field.
To follow this recipe, open B05527_03 – STARTER.twbx. Use the worksheet called Dynamic Dimension, and connect to the MasterTable (FlooddataMasterListrev) data source.
The following are the steps to create a chart with dynamic dimensions:
- Right-click the arrow beside the Dimensions section in the sidebar, and select Create Parameter.
- Create a string parameter called
Dimension to Displaywith the following settings:
- Show the parameter control for Dimension to Display. You can do this by right-clicking on the parameter, and selecting Show Parameter Control.
- Create a calculated field called
Selected Dimension, with the following formula:
- From Dimensions, drag the calculated field Selected Dimension to the Rows shelf.
- From Measures, drag Damage $ (USD) to the Columns shelf.
- Right-click on the SUM(Damage $ (USD)) pill in the Columns shelf, and select Format.
- In the side bar, change the Numbers in the Scale section, and select Currency (Custom) with the following additional settings:
- Zero (
0) decimal places - Units in Billions (B)
- Zero (
- Hover your mouse pointer over the Damage $ (USD) axis and click the sort icon once to sort the bars in descending fashion.
- Test the parameter. Ensure that as you change the parameter selection, the labels for the bars also change.
Normally when we create our charts, we choose the dimensions and measures to display and place them on the appropriate shelves or cards.
For example, if we wanted to show flood damage by Country, our Rows and Columns shelves would look like this:
If we wanted to show it by Main Flood Cause, we would replace the Country pill in the Rows shelf with Main Flood Cause:
What we have done in this recipe is introduce a parameter so that our end users can decide which dimension they want used in the Rows shelf. The parameter called Dimension to Display elicits the choice from our end users. Once the choice is made, a calculated field identifies which dimension should be used:
This calculated field needs to replace the dimension pill we have in the Rows shelf.
With the calculated field Selected Dimension in place, what is being displayed can now be dynamic, as the dimension changes when the parameter choice changes.