Adding calculated measures for worksheet and dashboard

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 allows you to create calculated measures directly from the visualization layer. These calculated measures can be created for both worksheets and dashboards. Before defining any calculated measures, ensure that you select the required semantic model. You can create, update, validate, and delete calculated measures using the available semantic models that are used in the workbook.

When working with a selected semantic model, you can write and validate MDX expressions as the semantic model designer does.

Important points to know 

• From Kyvos 2023.3 onwards, calculated measures are supported in the HOLAP semantic model. For this, you must ensure the following: 
  • The value of the kyvos.query.holap.calculated.measures.enabled property must be set to true.
  • The query will run successfully only when materialized fields are included in the view, filter, calculated measure, or non-empty behavior.
  • The query fails if non-materialized fields are used in the view, filter, calculated measure, or non-empty behavior.
  • Do not change any field to non-materialized after the semantic model is processed. 
  • This feature is only applicable to Power BI and is not available to the Kyvos Web Portal.

• If you have required permissions on a workbook (such as CRUD operations), you can create calculated measures. However, if you do not have the necessary permissions, the option to create calculated measures on the UI will be unavailable.

• All the calculated measures are stored in the workbook. 

• All the available calculated measures in the workbook are available in different areas of the worksheet and dashboard, such as shelves, filter cards, sort dialog, and highlight dialog, based on the applicable semantic model associated with that worksheet.

• A worksheet lists all the calculated measures and its group for the selected semantic model.

• There will not be any data security (column level security) on the calculated measures created at the workbook level.
• You can also create, update, delete, and rename calculated measures and its group at the workbook level using REST and JAVA APIs. 
• If raw data query is enabled for the semantic model, the calculated measures created with the semantic model will not be available on the worksheet.

To add a calculated measure, perform the following steps. 

1. From the Toolbox, select  Workbooks, then select a workbook and worksheet.
2. Use the  Design  workspace.
3. On the right side of the window, click Action menu (...), and then select Calculated Measures. 
   
   The Calculated Measures dialog is displayed. This will allow you to create calculated measures for all semantic models used in the workbook. 
   
4. Click the arrow to Add Measure Group. A calculated measure is automatically added and selected when adding a calculated measure group.
5. In the Properties section, you can modify the group name and add a description if needed. You can modify the measure group properties at any time. Select the measure group first, and then edit the properties as needed. 
   
   
6. Select the required Semantic Model from the list. 
   
7. Add a measure using the Plus icon. The measure is added to the calculated measure group. 
   
8. In the Properties pane, you can specify the required properties for the calculated measures. 
9. Start typing an expression in the editor section of the dialog box.  As you enter text, suggested submodel expressions, semantic model, dimensions, members, and so on are displayed. You can also see a description of the selected function. To view a list of functions along with the detailed description, navigate to the Functions  tab and select a function from the list.
10. To move a measure from one group to another, select the measure, and then in the Properties section, select the required group from the Measure Group list in which you want to move the measure. 
11. Before saving the calculated measures, click Validate to verify the calculated measure. You can also click Validate All to verify all calculated measures. 
12. Click Apply. The calculated measures are applied to the worksheet. You can also use the calculated filters on the Dashboard. 

The following worksheet illustrates calculated measures.

The following dashboard illustrates calculated measures.

Deleting calculated measures 

You can easily delete calculated measures. 

To remove the calculated measures, perform the following steps. 

1. On the right side of the window, click Action menu (...), and then select Calculated Measures. The Calculated Measures dialog is displayed. 
2. On the Calculated Measures dialog box, in the Calculated Measures pane, right-click the calculated measure that you want to remove, and then click Delete. 
   
3. On the confirmation dialog box, click Delete. The calculated measure is deleted.