Solution: Workout Wednesday2022 Week 48: Can you do this in ONE dashboard?
Focusing on Dynamic Zone Visibility
I created this visualization by utilizing three worksheets, all integrated into a single dashboard, as per the requirement.
Creating KPI Worksheets:
1) Connect to the Sample Superstore dataset and drag the Orders table.
2) Creating new Measures:
Customers
COUNTD([Customer Name])
Orders Count
COUNTD([Order ID])
Cities
COUNTD(City)
Products
COUNTD([Product Name])
Dummy True
TRUE
Sample superstore dataset has a table name Orders. So, I created the measure Orders Count instead of Orders. Right Click on Measure Names in the Data Pane, click on Aliases and change the alias of Orders Count to Orders.
Drag Measure Names to Filters and select Customers and Orders (We can find the alias of Order Count here).
Drag Measure Names to Columns. Add both Measure Values and Measure Names to Text in the Marks card. Click on the Text field, format the text, and change the alignment to Center Middle.
Set view to Entire View.
Double-click the Rows shelf and enter MIN(1.0). Right click on MIN(1.0) axis ->Edit Axis and change the range of the axis to Custom. Then Right click on MIN(1.0) axis and uncheck Show Header.
Change mark type to Bar. Increase the size of the bar by adjusting the size slider in the Marks card.
Remove any tooltip from showing by unchecking Show Tooltips on the Tooltip Shelf. Set Grid Lines, Zero Lines, Axis Rulers, and Row Dividers to None. Set the color of Column Dividers in Pane to white color and level to highest.
Drag Measure Names to Color. Set colors for Customers and Orders. Match font colors to bar colors.
Drag Dummy True field to Detail. Rename the Sheet KPI1.
3) Repeat the process ( or Duplicate the KPI1 sheet and change as required) for creating another sheet for Cities and Products measures. Name the sheet KPI2.
4) Creating the Bar Chart:
Create 2 new parameters:
a) pSelected KPI
b) pShow Detail? (We will use it later)
Create 2 new calculated fields:
a) Selected KPI
CASE [pSelected KPI]
WHEN ‘Customers’ THEN [Customer Name]
WHEN ‘Orders’ THEN [Order ID]
WHEN ‘Cities’ THEN [City]
WHEN ‘Products’ THEN [Product Name]
END
b) Dummy False
FALSE
On a new sheet, add Selected KPI to Rows and sort by Sales descending. add Sum([Sales]) to Columns. Drag both Selected KPI and Sum([Sales]) to Label in the Marks card. Format the text as required ensuring it is left aligned. Set the font to Match Mark Color. Adjust the width of the row as needed.
Add pSelected KPI to Color and change the color of all the KPIs by changing the value of pSelected KPI to different measures (Customers, Orders, Cities, and Products).
Remove all axis and header columns (by unchecking Show Header), all gridlines, all row / column dividers. Add a Title to the chart using pSelected KPI parameter.
Drag Dummy False field to Detail. Rename the sheet Detail.
5) Creating the Dashboard:
Set the dashboard size to a fixed 600 px by 600 px.
Drag a vertical container to the dashboard. Set the container to be floating and adjust its size to 600 px by 600 px.
Inside the floating container, drag another vertical container and set it to be tiled. This container will hold the KPI1 and KPI2 sheets.
Drag the KPI1 sheet and then the KPI2 sheet (below KPI1) into the tiled vertical container. Hide the titles of both KPI1 and KPI2 sheets.
Drag the Detail Sheet onto the dashboard. Set it to be floating and adjust its size to 600 px by 600 px. Set the background color of the Detail Sheet to white.
We will have a dashboard like this:
6) Set Parameter Actions:
a) Update pSelected KPI action:
b) Show Detail action:
c) Show Overview action:
7) Setting Dynamic zone Visibility:
Select Detail sheet in the dashboard, go to Layout. Choose Control Visibility using Value checkbox and select pShow Detail? Parameter.
