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.

Kyvos supports 'Ask Kyvos Copilot', which uses Large Language Model (LLM) to create MDX queries from natural language. With this feature, you no longer need to refer to the MDX documentation to get help with writing MDX expressions. All you need to do is use sample input or provide natural language input about the MDX query, and an MDX query will be generated. This integration saves time and effort when creating MDX queries. For more details, see the Using Kyvos Copilot for creating MDX calculations and querying section.

Important points to know

• From Kyvos 2023.5 onwards, calculated measures are supported in the HOLAP semantic model for Kyvos Viz.

• From Kyvos 2023.3 onwards, calculated measures are supported in the HOLAP semantic model for Power BI. 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 applicable to Power BI and and 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.

Adding calculated measures

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.

   Open

   

   The Calculated Measures dialog is displayed. This will allow you to create calculated measures for all semantic models used in the workbook. 

   Open

   

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. Click the Ask Kyvos Copilot link. The Calculations using Kyvos Copilot dialog is displayed.

7. On the Calculations using Kyvos Genie dialog, you can do the following:

   1. Category: Select the required type of category for calculations, such as General Calculations, Arithmetic Calculations, or Time-series Calculations.

   2. Sample Input: When selecting a category, use the Sample Input displayed on the right side of the dialog.

      1. Use Me: Hover over the sample input shows the Use me or Copy to Clipboard options. To use the sample input for calculations, click the Use me option. The selected sample input is displayed in the Input Prompt field.

      2. Copy to Clipboard: You can also copy the sample input using the Copy to Clipboard option.

   3. History: The history of the last 30 days is stored and can be filtered by category or custom text. You can also reuse previous inputs and outputs to create new expressions.

   4. Click Generate. The MDX output is then generated accordingly.

   5. Click Accept. The MDX expression is displayed in the Calculated Measures dialog box.
      
      

7. Select the required Semantic Model from the list.

8. Add a measure using the Plus icon. The measure is added to the calculated measure group. 

9. In the Properties pane, you can specify the required properties for the calculated measures. 

10. 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.

11. 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. 

12. Before saving the calculated measures, click Validate to verify the calculated measure. You can also click Validate All to verify all calculated measures. 

13. 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 as shown in the View mode.

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.