Group and Classify Measures in a Calculated Table

Hi Anonymous 

Yes, understood 🙂 You are wanting to group the measures within a visual rather than allow the user to select measures by filtering.

The methods I suggested are intended to allow grouping within a visual, but also happen to be applicable to measure selection in general. Grouping within a visual is really a form of selection where each "row" of the visual is a selection corresponding to the filters applying to that row (due to the grouping columns).

 

I have attached a PBIX showing both methods.

 

1. A calculation group called Measure Selection.

1. Each calculation item corresponds to a measure, identified by the calc item column Measure Name.
2. Includes grouping columns Report Group and Metrics Group (and associated Ordinal columns). These can only be created as calculated columns in a calculation group. In this case I retrieved the values from the table mentioned below (Measure Selection Table).
3. A format string is defined for each calculation item.

 

2. An imported table called Measures Selection Table.

1. Each row corresponds to a measure, identified by the column Measure Name.
2. Exactly the same structure as the Measure Selection calculation group.
3. The measure Selected Measure uses SWITCH to select the appropriate measure based on the value of Measure Name (similar to danextian's example).
4. This measure has a dynamic format string to select the appropriate number format per measure.

Measure Selection measure definition:

 

Selected Measure = 
SWITCH (
    SELECTEDVALUE ( 'Measure Selection Table'[Measure Name] ),
    "Quantity", [Total Quantity],
    "Sales Amount", [Sales Amount],
    "Cost", [Total Cost],
    "Margin", [Margin],
    "Average Price per Unit", [Average Price per Unit],
    "Average Cost per Unit", [Average Cost per Unit],
    "Average Margin per Unit", [Average Margin per Unit],
    "Cost %", [Cost %],
    "Margin %", [Margin %]
    -- Otherwise BLANK
)

 

and the dynamic format string expression:

 

VAR ZeroDecimals = "#,0"
VAR TwoDecimals = "#,0.00"
VAR PctTwoDecimals = "0.00%"
VAR Result =
    SWITCH (
        SELECTEDVALUE ( 'Measure Selection Table'[Measure Name] ),
        "Quantity", ZeroDecimals,
        "Sales Amount", ZeroDecimals,
        "Cost", ZeroDecimals,
        "Margin", ZeroDecimals,
        "Average Price per Unit", TwoDecimals,
        "Average Cost per Unit", TwoDecimals,
        "Average Margin per Unit", TwoDecimals,
        "Cost %", PctTwoDecimals,
        "Margin %", PctTwoDecimals
        -- Otherwise BLANK
    )
RETURN
    Result

 

To apply these in the report:

1. For the calculation group, place an arbitrary measure in Values (it is over-ridden by calc items), and grouping columns + Measure Name on Rows, plus required columns on Columns.

2. For the table with SWITCH measure, similar setup using the columns of Measure Selection Table, with Selected Measure in Values.

The reason that a field parameter containing measures with a hierarchy of grouping columns is unsuitable for grouping in a visual is that the list of measures displayed in the visual is defined once at the visual level, and Power BI won't selectively include the measures within a particular grouping of the visual. The end result is the same as if the full list of measures had been included in Values.

 

If you have a field parameter table defined, you can certainly write a SWITCH measure referencing that table as danextian showed, but this doesn't make use of any special features of the field parameter table, and is really the same as my method #2.

 

That's my take on it anyway. Hopefully that helps a bit! 🙂

 

Regards

Hi Anonymous 

To get the desired result, you need to expand the hierarchy further. Regular Giving shows a value because it has only one measure name, so SELECTEDVALUE returns that single measure's order. However, Gift in Wills has seven measure order values, causing SELECTEDVALUE to return blank. The SWITCH measure relies on the value returned by SELECTEDVALUE of measures order, so if the current row results in a blank, the measure will also return blank.