Today, I want to create a dashboard that consists of two worksheets stacked on top of each other. I'm using a parameter control here in the top right corner to switch between the Sales worksheet and the Profit worksheet, and I’m going to show you how we do that.

Overview of the Setup

Before we create everything, I want to do a quick overview. Here, you see the Profit worksheet. It has the green bars, and I have Profit on the Rows shelf. Over on the right, you see my parameter control which has two values: Sales and Profit. Currently, Profit is selected. On the Filters shelf, I have the Sheet Calc, which is a calculated field pointing to the parameter; right now, it’s set to Profit.

The second worksheet is identical, except everything points to the Sales worksheet. The sheet itself is named Sales, the parameter is set to Sales, and Sales is on the Rows shelf. In the filters, you can see that the calculated field is filtering to Sales.

Step 1: Create the Parameter

The first thing I need to do to make this work is create the parameter and a calculated field. In the Data pane at the top, I click the drop-down arrow and select Create Parameter.

I’m going to name this pSelect a Sheet. For the Data type, I’m going to pick String. Then, under Allowable values, I select List and type in Sales and Profit (the exact names of my worksheets). I click OK, and you can see the new parameter under the Parameters section on the left. I’ll click the drop-down arrow on that parameter and select Show Parameter. To make it look better, I’ll change the Parameter Control style to a Single Value List.

Step 2: Create the Calculated Field

Now I need to add my calculated field. In the Data pane, I choose Create Calculated Field. I’ll call it Sheet Calc and add the parameter I just created, pSelect a Sheet, to the formula. It appears in purple, indicating it is a parameter. Once it says the calculation is valid, I click OK.

Step 3: Apply the Filters

Now I want to use that on the Filters shelf. I’ll drag Sheet Calc onto the Filters shelf for the Sales worksheet. When the Filter Dialog opens, I choose Custom Value List, type "Sales" into the yellow bar, and click the plus (+) sign to add it.

I’ll repeat this for the Profit worksheet: drag Sheet Calc to Filters, choose Custom Value List, type "Profit," and click the plus (+) sign. Now, each sheet is restricted to only appear when its corresponding name is selected in the parameter.

Step 4: Build the Dashboard

In the bottom tabbed area, I click New Dashboard. I want these worksheets to be floating so I can stack them exactly on top of each other.

1. Drag the Profit worksheet onto the dashboard and resize it.

2. Move the Parameter Control to the top right corner.

3. Drag the Sales worksheet onto the dashboard, select "Sales" in the control so the bars appear, and resize it to take up the same space.

Tip: You may want to hide the Titles for both worksheets if you are lining them up perfectly.

Testing the Result

To test it, simply click on the Parameter Control. You should see the dashboard seamlessly switching between the Sales and Profit views. This is a powerful way to save space on a dashboard while giving users control over their view.