Field Parameters: A Case of Measure Groups

In the last article I showed you how Field parameters could be used to replace the calculation groups, particularly when creating a finance report structure.

Today let me show how you can use Field Parameters to group measures in order to display a different combination of those measures based on the different slicer selections. Sounds cool, right?

There is this new guy Matt who is responsible for updating his company's infrustructure and migrating the processes from an outdated tool to Power BI.

The tool his company uses gives a lot of flexibility to pick and choose different areas of analysis and have lots of different views of the data.

Matt likes the idea of flexible solutions, so he created a table visualisation driven by two Field Parameter Slicers, one single selection Ananlysis Area slicer and the other one Metrics slicer, allowing to choose as many measures/table columns in any order and any combination.?

The management appreciates the effort, but asks to create pre-defined groups of the columns to be shown in a table, since it requires fewer clicks than picking the individual metrics in the slicer and resembles to the user experience of the previous tool.?

So, let show Matt how we can solve this requirement with Field parameters.

STEP 1: Create a Field Parameter adding a couple of measures you would like to display.

STEP 2: Adjust the formula for the parameter using the following recipe:

Ingredients:

0. Start with commenting which group of measures you would like to define. In our case the groups contain the names of the columns to be displayed: group 1 is "Total Sales", group 2 is "Total Sales, Costs", group 3 is "Total Sales, Costs, Variance" etc.

1. Input the Name for your measure (The name that will be displayed in the table visualisation)
2. Input the Measure itself

NAMEOF(YOUR MEASURE)

1. Add Index (it will reference the order of all elements)
2. Input the name of the group (this will go into your slicer on the page)

"Total Sales", "Total Sales, Costs", "Total Sales, Costs, Variance"

1. Add the Index for the measure (this can be used later on to create futher measures using switch function)

"Total Sales"-1, "Costs"-2, "Variance"-3, "Variance %"-4, "Total Sales YTD"-5,etc

6. Add the index for the group.

"Total Sales"-1, "Totals Sales, Costs"-2, "Total Sales, Costs, Variance"-3, etc

STEP 3: Let's go ahead and rename those columns:

Example: Value4-Metric Group Name, Value5-MetricId, Value6-GroupId

STEP 4: Sort the column Metric Group Name by GroupId

Add Metric Group Name to a single select slicer

STEP 5: Add the Metrcis Parameter to your table viz.

STEP 6: Let us get some formatting done on Variances and their % values.

Go to the viz formatting options and navigate to Cell elements, there you can adjust the conditional formatting. No worries, if you have, let's say 4 measures Variance, Variance %, Variance YTD, Variance YTD% you will have to adjust them all once and the formatting will remain irrespective of the Metric Group Name Chosen.

STEP 7: Enjoy using your pre-defined measure groups in the table.

Now that the management have seen the successfull implementation of the pre-defined measure groups. They got really excited about the mighty power of Field Parameters and want to go one step further and ask Matt for other types of representating their data, in which Measure names have to be placed on the ROWS!

Let's help Matt shine like a diamond and deliver this solution in no time.

STEP 1: Create a measure using SWITCH by the MetricId. In the example below that is my Total Sales, Costs and Variance, the list will continue as i am going to be adding Variance%, YTD, LY values. Just make sure you place the same MetricId as in your parameter.

STEP 2: Once you are done, add Year, Metrics Parameter and Actuals to your Matrix viz and set the Metrics Parameter to show selected field.

And here we go, we got the first requirement fulfilled. Wait and how about the conditional formatting for the Variance for example?

STEP 3: Here we would need to create a measure called Selected font, where we can specify the Fields that need to get conditionally formatted.

Add the conditional font formatting by Field Value:

And here is the result:

What about getting this view up-to-speed?

Obviously here you would need to create calculations for Last Year and Variances based off what we already created in Actuals.

Color Actuals and Last Year by Selected font value like in a previous example. Color Variances by the rules.

I am sure we can find way more optimal and interesting ways to use Field Parameters as we engange more and more with them, but at the moment I simply enjoy having this option that gives the users more room to play and create reports and view the data the way they want to.