I've created a series of fairly complex charts that have their unique conditions applied to each of them. A sample notebook is attached and I'd like to pull down totals from each row.
Because of each of the filters applied, the totals will change depending on the value the user inputs. Attached is a dashboard view.
Hi @Katherine Bell
This can be solved using parameters, calculated fields, and parameter actions.
1.Create parameters Region 1, Region 2, and Region 3 with a data type of string and all allowed values.
2.Next, from the Actions tab in the Dashboard, create three parameter actions with target parameters that correspond to the parameters you just created.
At this time, for the source sheet, select only select region 1, select region 2, or select region 3 from the sample dashboard, depending on the parameter action you are creating.
3.Once you have set the parameters and parameter actions for Region1, 2, and 3, create a calculated field.
IF [Region 1] = 'Central' THEN SUM(IF [Region] = 'Central' THEN [Sales] END )
ELSEIF [Region 1] = 'West' THEN SUM(IF [Region] = 'West' THEN [Sales] END )
ELSEIF [Region 1] = 'South' THEN SUM(IF [Region] = 'South' THEN [Sales] END )
ELSEIF [Region 1] = 'South' THEN SUM(IF [Region] = 'East' THEN [Sales] END )
END
+
IF [Parameters].[Region 2] = 'East' THEN SUM(IF [Region] = 'East' THEN [Sales] END)
ELSEIF [Parameters].[Region 2] = 'West' THEN SUM(IF [Region] = 'West' THEN [Sales] END )
ELSEIF [Parameters].[Region 2] = 'Central' THEN SUM(IF [Region] = 'Central' THEN [Sales] END )
ELSEIF [Parameters].[Region 2] = 'South' THEN SUM(IF [Region] = 'South' THEN [Sales] END )
END
+
IF [Parameters].[Region 3] = 'South' THEN SUM(IF [Region] = 'South' THEN [Sales] END )
ELSEIF [Parameters].[Region 3] = 'West' THEN SUM(IF [Region] = 'West' THEN [Sales] END )
ELSEIF [Parameters].[Region 3] = 'Central' THEN SUM(IF [Region] = 'Central' THEN [Sales] END )
ELSEIF [Parameters].[Region 3] = 'East' THEN SUM(IF [Region] = 'East' THEN [Sales] END )
END
4.Apologies for the lack of formatting, but if you add a new sheet with the calculated field you just created to a dashboard, a dynamic total will be displayed.
Thank you.
