Skip to content

multifact-metrics.md

Latest commit

 

History

History
105 lines (58 loc) · 4.04 KB

multifact-metrics.md

File metadata and controls

105 lines (58 loc) · 4.04 KB
Raw
copyright
years
2025, 2026
lastupdated 2026-05-14
keywords multi-fact metrics, multiple tables, calculate after aggregate
subcollection watsonx-bi

{{site.data.keyword.attribute-definition-list}}

Creating calculations for cross-metric queries

{: #multi_fact_metrics}

You can create calculations that help answer questions that span multiple metrics. These calculations are based on a multi‑fact metric, which combines measures from different fact tables. Multi‑fact metrics are especially useful when you need to compare or analyze data across fact tables that share common dimensions. {: #shortdesc}

Before you begin

{: #prereq_multi_fact}

Before you create a multi-fact metric or multi-fact calculation, make sure that:

  • You have at least two fact tables in your semantic data model

  • Measures are already defined in each fact table that you want to use

When you query metrics that come from different facts, watsonx BI does not join them on shared dimensions. You can ask questions on shared dimensions through calculations that are created for cross-metric queries. {: note}

Step 1: Create a metric definition from multiple fact tables

{: #create_multi_fact}

To create a calculation that spans multiple metrics, you must first define a metric which contains measures from the multiple fact tables.

  1. Open the semantic data model that you want to create the metric in from Data and Metrics.

  2. Go to Advanced mode.

  3. In the semantic data model tree, select measures from the different fact tables and from the context menu, select New > Metric definition.

  4. Specify the details for the metric definition, include the data scope and measure role.

  5. Click Done to finish.

Step 2: Create a calculation from mult-fact metric definition

  1. Select the metric definition that you just created and from the context menu, choose New > Calculation.

  2. Define your calculation by using the measures within the metric definition. For example, Sales_Target - Sales_Total.

  3. Select Calculate after aggregation.

    When Calculate after aggregation is selected, the calculation is performed after values are aggregated. This is required for multi‑fact metrics so that the results from each fact table are aggregated first and only then included in the calculation. {: important}

  4. Select OK to save the calculation.

  5. Select Actions > Save to save your semantic data model.

  6. Select the metric definition and export it to make it available in conversations.

Example: Variance from plan calculation

{: #example_variance}

Consider a scenario where you have:

  • Sales fact table with a Sale_Total measure

  • Sales Target fact table with a Sales_Target measure

  • Time dimension that joins both fact tables

To create a Variance from plan calculation that shows the percentage of actual sales compared to target:

  1. Create a metric definition by using measures Sale_Total and Sales_Target measures from the Sales and Sales Target tables.

  2. Select the metric definition in the semantic model and create a calculation named Variance from plan. Use the expression:

    100 * Sale_Total / Sales_Target

    {: codeblock}

  3. Select Calculate after aggregation and click OK.

    This metric calculates the variance by first aggregating the sales totals and targets separately, then performing the division and multiplication to get the percentage variance.

  4. Save the semantic data model and export the metric definition to the project.

When you ask a question in a conversation that uses this metric, {{site.data.keyword.wxbia_short}} uses the calculation that you created and aggregates the expression according to the context of your question.

Related links

{: #related_multi_fact}