Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Optimize / Fix calculation

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.

1 Solution

Accepted Solutions
Partner - Contributor II

*= 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.

4 Replies
Partner - Specialist

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

Partner - Contributor II

*= 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.