Challenge, WorkoutWednesday, LOD Calculations, Table Calculations

By: Rosario Gauna @rosariogaunag

In week 41 of Workout Wednesday, @AnnUJackson prepared us a challenge to recreate a dashboard with a design oriented for those end users more interested in obtaining immediate information, without time to deepen, but that requires to quickly visualize data with context.

Link to challenge

The challenge is to create a control panel that highlights different panes, based on six options to be chosen by the user. Strategically placed labels are also displayed to provide context information, such as: total panel sales and the last monthly sales value per panel.

Next, I share the procedure that I used:

Step 0: Create Extract of the data, selecting the data for the years 2017 and 2018.

Step 1: Create the “SHOW ME” parameter, of the integer type, for the user to select the desired highlighting option.

The options are:

 Value Display As 1 TOP CATEGORY PER REGION 2 BOTTOM CATEGORY PER REGION 3 TOP & BOTTOM CATEGORY PER REGION 4 TOP OVERALL 5 BOTTOM OVERALL 6 TOP & BOTTOM OVERALL

Step 2: Calculate the value of the “Sales by Category & Region”.

 Sales by Category & Region = {FIXED [Category], [Region] : SUM([Sales])}

Step 3: Calculate a field that contains the value of the Region, when the highlighted option is selected at the Region level, otherwise, assign the value of “Overall”.

 Region or Overall = IIF([SHOW ME] < 4, [Region], “Overall”)

Step 4: Calculate the value fields of the Top and Bottom sales.

The “Top Sales” and the “Bottom Sales”, can be calculated by 1) level region or 2) overall, according to the option selected by the user.

 Top Sales = {FIXED [Region or Overall] : MAX([Sales by Category & Region])} Bottom Sales = {FIXED [Region or Overall] : MIN([Sales by Category & Region])}

Step 5: Calculate the variable “Color”, to highlight the panels according to the user’s selection.

For its definition, first remember the possible values defined in the parameter “SHOW ME” and when its value indicates that the “Top” values will be highlighted, or when its value indicates that the “Bottom” values will be highlighted, or when its value indicates that they will highlight both.

Below are two possible options for its calculation, the first, using directly the value of “SHOW ME” and the second, using the remainder of dividing “SHOW ME” by 3.

 CONDITION USING THE VARIABLE “SHOW ME” USING THE REMAINDER OF DIVIDING “SHOW ME” BETWEEN 3 The value of “Top” is assigned, if it meets the following conditions: ·       The sales of the Category – Region are equal to the “Top Sales”. ·       In “SHOW ME” one of the options to highlight the “Top” is selected. IIF( [Sales by Category & Region] = [Top Sales] AND NOT ([SHOW ME] = 2 OR [SHOW ME] = 5), “Top”, IIF( [Sales by Category & Region] = [Top Sales] AND [SHOW ME] % 3 <> 2, “Top”, The value of “Bottom” is assigned, if it meets the following conditions: ·       The sales of the Category – Region are equal to the “Bottom Sales”. ·       In “SHOW ME” you have selected one of the options to highlight the “Bottom”. IIF( [Sales by Category & Region] = [Bottom Sales] AND NOT ([SHOW ME] = 1 OR [SHOW ME] = 4), “Bottom” IIF( [Sales by Category & Region] = [Bottom Sales] AND [SHOW ME] % 3 <> 1, “Bottom”, The value of “Neutral” is assigned, in case of not fulfilling any of the previous conditions. , “Neutral”)) “Neutral”))

Step 6: Calculate the labels that appear at the top of each panel.

The first label will contain the text “Total Sales” and the second label will contain the value of the sales. The labels will only be assigned to the twelve-month displayed in each panel. And two different labels are created, to handle different format in each one of them.

 Label1 = IIF(Index() = 12, “Total Sales”, NULL) Label2 = IIF(Index()=12, AVG([Sales by Category & Region]), NULL)

Both variables must be table calculations defined using “Pane (across)”.

Step 7: Create the chart.

The first chart is of type of area, to give the shading effect of each panel. The value to be plotted will be one.

The second chart is line type and the value to chart is the monthly sales.

Both charts will be combined in a double axis – not synchronized.

Move to the shelves of:

• Columns   ⬅”Upper([Region])”, Month of Order Date
• Rows  ⬅”Upper([Category])”, AVG(1), “Sales”

In the area chart, include the following variables

• Color  ⬅”Color”
• Label   ⬅”Label1″, “Label2”
• Tooltips  ⬅”Sales”

Adjust the vertical axis of AVG (1), between the values of zero and one.

Adjust the horizontal axis of the months, between the values of 1/1/2017 and 12/1/2017.

To display the label corresponding to the last month’s mark, in the line chart, on the “Label” shelf activate the option “Show Mark Label” and select the option “Line Ends” and deselect the option “Label start of line “.

Note: The area chart can be replaced with a bar chart, obtaining the same shaded effect.

Step 8: Adjust the tooltips and the formats.

Ready!

I take this opportunity to thank @AnnUJackson, who always offers us fun challenges, full of details and beautiful designs.

If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)

Regards!

Rosario Gauna