Measure-based Calculations in Aggregated Table

[michael_ross]

EDIT: A new feature called "Insight over Result Sets" has been added that helps address this issue. See my comment below from 4/6/22 for more details.

It would be nice to be able to be able to reference the results of a measure in a separate measure to make variance calculations easier in an Aggregated Table. There can be cases where measure columns have different filters or nested aggregations that make comparing two values difficult or impossible in the current formula editor.

Imagine an example where we have four measures: Actuals, Budget, Variance, and Margin %. We can create measures that have the appropriate filters on them for Actuals and Budget, but it would be great to reference the results of those measures in the Variance and Margin % measures. We should be able to have a formula that is Actuals - Budget for Variance, and Variance / Budget for the Margin %.

There are ways to get around this at the Schema and Business Schema layers today, but it would be great if this could be solved within the Insight itself.

[renukaprasad]

Hi,

Use  formula for the Variance and Margin in insight refer the below screen shot

Step1: Add formula to the Measure

Step2: enter the formula  in the formula bar

Step 3: Validate the formula and close

Step: Rename the formula name

[michael_ross]

Correct, when the columns used in the variance calculations are simple, that is the way to do the variance. I'm referring to more complex situations like I ran into with a prospect where the actuals and budget formulas themselves had to include sign flipping logic based on specific dimension values and filters on specific accounts. The complex case statements I had to build just to get the Actuals and Budget values did not aggregate properly within the Insight when I had to do the variance calculations. I ultimately had to create an Incorta Analyzer Table in the Schema to do all the necessary pre-aggregations of the data.

It would have been much easier if I could've created a formula within the Insight that referenced the fully aggregated amounts that were in the first two columns. A user that can create Insights, but does not have edit access to the Schema, would not have been able to successfully build an Insight that returns the expected amounts.

[michael_ross]

The "Insights over Result Sets" feature released in version 2022.4 helps solve the problem I described above. It is now easy to define the complex aggregations in the result set, and quickly reference them in the Insight. Variance, "% of total", and other similar types of calculations are now much easier to create.

Here's a link to the documentation that describes the feature:

https://docs.incorta.com/cloud/references-visualizations-insights-over-result-sets/

[KailaT]

This idea has been delivered. Thank you for your suggestions!

[michael_ross]

Here's a more detailed blog post about the Insights over Result Sets feature that solved my problem.

https://community.incorta.com/t5/Dashboards-Analytics/Insights-over-Result-Sets/ta-p/1118