Adding a dynamic measure format

Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Kyvos supports a Dynamic Measure format that can be applied to a worksheet or Dashboard. When the value of a dimension filter is changed, the measure format of the calculated measures will automatically update to reflect the selected value of the dimension filter. See the Filtering data in worksheets section for more details.

To make the Dynamic Measure format work, you need to create the expressions for the following:

Calculated measure: Displays the Measure value when the filter value is changed.
Format measure: Displays the Measure format along with the Measure value. You can also reuse the Measure format for other Measure values.

Sample Expression

For example, you have a semantic model with a dimension called 'Calculation Type,' and this dimension has members such as MTD, YOY, YTD, and so on. Multiple calculated measures are created in the semantic model, and their values depend on the Calculation Type filter selected in the view. When you modify the filter value, the format of the calculated measure will change accordingly.

Consider an example where a Calculated Measure displays a dynamic format, i.e., DYNAMIC_FORMAT = [Measures].[FormatMeasure]

Following is the sample expression for a calculated measure

iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[YOY],
[Measures].[YOY Sales],
iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[YTD],
[Measures].[YTD Sales],
iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[MTD],
[Measures].[MTD Sales],null))), DYNAMIC_FORMAT = [Measures].[FormatMeasure]

Following is the sample expression for a format measure

iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[YOY],
"$#,##0.00",
iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[YTD],
"0.00% ",
iif([CalculationType].[CalculationType].currentmember is [CalculationType].[CalculationType].&[MTD],
"#,##0.000000" , “#,##0.00”)))

If the filter is set to 'YOY' and the 'YOY Sales' Measure is visible in the View, the Measure format will be '$#,##0.00'.

Filter: Calculation Type = YOY
Measures: Sales Cal
Measure Format: "$#,##0.00"

Note

The changes are specific to the Kyvos Visualization layer and will not impact any third-party BI tool.
Dynamic Measure Format will be properly evaluated only if a single member of the Calculation Type Hierarchy is specified in the form of IS filter or INLIST filter.
If the filter is not applied to the calculation type or it is applied to multiple members of the hierarchy, then the default format string in calculated measures will be displayed.
The dynamic format will be displayed for measures with the expression DYNAMIC_FORMAT = [Measures].[Format Measure] present in them. If this string is not present in any expression, then the calculated measure format string will be used.
Kyvos supports naming semantic model entities in the ENTITY_ format, such as DIMENSION_, DIM_, MEASURE_, and so on.

To add a dynamic format measure, perform the following steps.

In the Measures column, select the measure.
In Measure Properties, select Expression.
On the Expression dialog box, specify the expression as per your requirement. Click the i icons to learn more.

Click Validate to validate your criteria.
If you have made changes and don’t want to keep them, click Cancel.
Click Save. The Dynamic Measure name and caption that you created are applicable to a Worksheet or a Dashboard.

Dynamic Format Measure- Worksheet

Dynamic Format Measure- Dashboard