Sometime users want the option to select different groupings for the the same metrics in the same table.
Suppose user wants to see the Sales numbers by Store Region, Product Type, and by Year based on this table.
First we need a Control elements on the page.
Then in the setting:
Control Type = Parameter
Value source = Create manual list
Control ID = param_GroupBy
(Important to note the control ID! Best practice to put “param” in the ID name itself, so we know it’s a parameter. Will be easier to reference in the formula down the line.)
In the Values section we have:
Text : Display Value
1 : Product Type (best practice to have this corresponds to the actual column name)
2 : Store Region
3 : Year
[uncheck] Exclude values
[uncheck] Allow multiple selection (we can only do one group by at a time for this example)
[optional] Show expanded list (instead of a dropdown this will show a selection box)
[optional] Show Null option
[optional] Required
Now we have our parameter control set up. We can add the new grouping column to table and reference the parameter control in a formula.
Select table, on the left panel, go to Groupings section, click on the +, in the menu add New column…
There should be a [Calc] column in the Groupings and a [Calc] Column in the table
In the formula bar for our [Calc] column, we need to use the Switch() function to reference the parameter control (Control ID) and return the correct columns from our table.
Switch([param_GroupBy], “1”, [ProductType], “2”, [StoreRegion], “3”, Text([DatePart Year]))
Switch(
[param_GroupBy] is the Control ID from the parameter
“1” is the text from the parameter
[ProductType] *is the column from the table
“2” is the text from the parameter
[StoreRegion] *is the column from the table
“3” is the text from the parameter
[DatePart Year] is the column from the table
)
Let’s give this [Calc] column a more meaningful name, [Group By].
And let’s bring our Sales column into the [Group By] calculations.
And it aggregate automatically…
And it should look something like this when it’s all done.
Please leave any feedback, comments, and critique in the comments below!