topic Re: Optimize / Fix calculation in App Development

Optimize / Fix calculation

Martin22 — Wed, 24 Jun 2020 09:43:00 GMT

Hi all,

I'm currently trying to implement a calculation, but it's not working the way I want.

The point is to have the calculation work as a KPI.
The problems I encounter are :
-I have a dozen "sub_categories" values to put in the calculation, and I would like to optimize the way it is written, while keeping how it works.
-The KPI is supposed to adjust to what data are selected : currently, the result stays the same whatever filter I choose.

Here is the calculation :

((sum({<[sub_category] = {'1'}>}[Flag]))
/ (sum({<[category] = {'A','B'}>}[Flag])))
*
((sum({<[sub_category] = {'1'}>}[measure])))

((sum({<[sub_category] = {'2'}>}[Flag]))
/ (sum({<[category] = {'A','B'}>}[Flag])))
*
((sum({<[sub_category] = {'2'}>}[measure])))

((sum({<[sub_category] = {'3'}>}[Flag]))
/ (sum({<[category] = {'A','B'}>}[Flag])))
*
((sum({<[sub_category] = {'3'}>}[measure])))

Can you help ?

Thanks,

Martin.

Re: Optimize / Fix calculation

Mauritz_SA — Wed, 24 Jun 2020 13:09:39 GMT

Hi @Martin22

By using set analysis you are effectively hard-coding the filter values for those fields. You can have a look at this blog article by @stevedark which I found really helpful on using Star Equals in set analysis which I think can assist you.

Hope it helps.

Regards,

Mauritz

Re: Optimize / Fix calculation

jsebrightqi — Wed, 24 Jun 2020 13:39:48 GMT

*= in the set analysis may help you to get different answers if you're making selections on the subcategories.

Thinking about how to simplify the expressions though-

It looks like you've got the following sum: (simplified a bit)

sum(Flag1) * sum(Measure 1) / sum(FlagAB)

+ sum(Flag2) * sum(Measure 2) / sum(FlagAB)

...

That could be simplified into

(sum(Flag1*Measure1)/sum(FlagAB) + sum(Flag2 * Product2) ) / sum(FlagAB)

Which can be simplified into

(sum(Flag1*Measure1) + sum(Flag2 * Product2) ) / sum(FlagAB)

Setting a variable with a parameter will allow you to simplify the writing of this a bit

Let vSubCatProduct = (sum({<[sub_category] = {'$1'}>} [ Flag] * [measure]));

Then your expression would be:

( $(vSubCatProduct(1))+$(vSubCatProduct(2)).... ) / (sum({<[category] = {'A','B'}>}[Flag])))

It's optional whether you put the (sum({<[category] = {'A','B'}>}[Flag]))) part into the variable or add it in the expression -if you display the subcategory/AB calculations separately then it would be better to do it in the variable.

Re: Optimize / Fix calculation

stevedark — Wed, 24 Jun 2020 16:32:50 GMT

Hi Mauritz - thanks for sharing that post.

Re: Optimize / Fix calculation

Martin22 — Thu, 25 Jun 2020 08:49:23 GMT

Hi,

Thank you for your answers, I'm going to try and see if I obtain what I'm looking for with both the set analysis and simplification, and I'll update the post.

Regards,

Martin.