Optimize / Fix calculation

[Martin22]

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.

[Mauritz_SA]

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

[jsebrightqi · Accepted solution]

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

[stevedark]

Hi Mauritz - thanks for sharing that post.

[Martin22]

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.