topic Re: SUM on MAX values in Dashboards & Analytics Discussions

SUM on MAX values

KHK — Wed, 05 Jul 2023 11:18:55 GMT

Hi,

I am migrating Power bI reports to Incorta.

In Power Bi report, they are fetching max value of one column and doing sum on that.

IN Incorta, i have created formula column in business view with max function and called this in measure column in Aggregated table. After applying sum aggregation, still the populated values not doing sum and showing max value in report total value place.

Please let me know how to do sum on MAX aggregation function.

Re: SUM on MAX values

RADSr — Wed, 05 Jul 2023 14:45:38 GMT

Selfish thing first - give this a kudo to draw attention to the issue: https://community.incorta.com/t5/product-ideas/add-sub-aggregate-v-subtotal-only-functionality/idi-p/4554

For now, you can do that with a pain-in-the-neck formula ( e.g. SUM( max(<c1>,groupby(<d1>,<d2>, etc )) + max(... )

Use a "result set" as the source for your insight producing the "max" values and then summing in the presentation insight:

Re: SUM on MAX values

anurag — Wed, 05 Jul 2023 15:23:11 GMT

Query over query is not supported in a formula.

Here Max is query # 1 and then SUM of MAX values is query # 2.

As @RADSr suggested you can do this by creating Resultset at the insight level.

In Resultset you will get MAX values and then insight over Resultset will sum output of the Resultset.

Re: SUM on MAX values

KHK — Wed, 05 Jul 2023 17:24:02 GMT

tried SUM( max(<c1>,groupby(<d1>,<d2>, etc )) + max(... ) in insight, and created same formula column in Business view ,and pulled resultset in insight, max function is not working and it is doing sum for all values.