Derived Measure

Derived measures is a special method to easily create complex measures based on existing measures. It's called Derived Measure. It is meant to be used for measures that are very similar and are related to a main measure. Like YTD, PY and also custom variants that can be defined by you and your specific wishes. In other solutions, you are forced to use all kind of C# scripts, not with derived measures. It works as follows:

Imagine we have a measure like Revenue and a YTD is needed as well. Normally you would have added another measure with YTD syntax, now you can just add derived measure to Revenue and set it to the template for YTD. The template is defined in the measuretemplates.yaml. It's especially beneficial if you have a lot of YTD measures, than you can just type this in the model.yaml, and you have YTD measure for basic measure:

- Name: Revenue
  Expression: |
    CALCULATE (SUMX(Finance, [Revenue])
  DerivedMeasures:
  - DerivedMeasure: YTD

The name for the new measure is automaticly set from the template. In the template the 0 is the placeholder where the name of the parent measure is filled in.

- DerivedMeasure: YTD
  MeasureName: "{0} YTD"
  Expression: "TOTALYTD([{0}], Kalender[Datum], All(Kalender))"

It's also possible to override the name for a given derived measure as follows:

- Name: Revenue
  Expression: |
    CALCULATE (SUMX(Finance, [Revenue])
  DerivedMeasures:
  - DerivedMeasure: YTD
    Name: Rev Year To Date with custom name

It is still possible to override also other settings as well. So if we want to set a measure to hidden, we can just define IsHidden: true. Default it will get the setting from the parent measure. Same for formatstring and other options as well.

Derived with parameters

It is also possible to supply parameters to the derived measure. This can reduce the number of measures with specific filters, let's take the example of budgets and le1, le2,and le3. (le = latest estimate).

- Name: Forecast
  Expression: |
    CALCULATE(SUMX(Budgetvalues, [Amount]))
  DerivedMeasures:  
  - DerivedMeasure: BudgetByType
    Parameters: 
    - Budgettype = Budget
  - DerivedMeasure: BudgetByType  
    Parameters: 
    - Budgettype = LE1
  - DerivedMeasure: BudgetByType
    Parameters: 
    - Budgettype = LE2  

The derived measure can look like this:

- DerivedMeasure: BudgetByType
  MeasureName: "Forecast amount {Budgettype}"
  Expression: "CALCULATE(SUMX(Budgetvalues, [Amount]), Budgettype[Type] = '{Budgettype}')"

The outcome for this will be 3 measures, with the following names:

Forecast amount Budget Forecast amount LE1 Forecast amount LE2

Also the the parent measure Forecast will exist. Multiple parameters are possible. Only named parameters are allowed and should start with a letter (a-z, A-Z).

Parameters work for:

• Expression
• MeasureName
• Description
• DisplayFolder

Order of applying settings

Parameters are applied in a certain order. It is possible to set identical settings at multiple levels. The order of applying is from the lowest level (derived measure level), measure template to the parent measure. This is applicable for measurename, description, data category, formatstring and displayfolder. Expression is always based on the measuretemplate.

Alternative example with parameters

- Name: Forecast
  Expression: |
    CALCULATE(SUMX(Budgetvalues, [Amount]))
  DerivedMeasures:  
  - DerivedMeasure: BudgetByType
    Parameters: 
    - Budgettype = Budget
  - DerivedMeasure: BudgetByType  
    Parameters: 
    - Budgettype = LE1
  - DerivedMeasure: BudgetByType
    Name: LE2 Amount
    DisplayFolder: {Budgettype} Values
    Description: The LE2 amount summed up for your convenience
    Parameters: 
    - Budgettype = LE2  

This will result in:

• Forecast amount Budget
• Forecast amount LE1
• Forecast amount LE2 Amount In a folder named LE2 Values with a description of The LE2 amount summed up for your convenience