Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
Please help to advise how to compute Others (sum of Product D & E only) in the same Revenue Range with Product C and excludes Product A & B in the year of 2019.
Thank you, Tracy
adding Product to the chart will validate the expression. if you only need the total revenue, just drop the Product dimension
Your sample seems giving right result (when one revenue is selected). However, you could try using simpler expressions like:
Exp 1 : Sum(Data)
Exp 2 : Sum({<Product=e(Product)>} Data) // if 'others' means not-selected/not in-scope ones
Dear Mr Tresesco
Thanks for your quick advice.
For Product C, I have changed to Exp 1 : sum(Data)
For Others, Exp 2 : sum({<Product=e(Product)>} Data) is NOT CORRECT
because it covers Products D& E (within the same Revenue Range 3,000000-3,999999) and Products A & B (outside the Revenue Range). Kindly advise how to exclude Products A & B.
Many Thanks, Tracy
I don't see any association between your product and range dimensions in data model. With this your solution might not work as expected.
Dear Tresesco,
Okay, what is your best advice ?
Thanks, Tracy
Correct your data model first.
Dear Tresesco
Sorry, not sure how to do it.
Tracy
from my understanding of the objective, you want the Revenue of Products with same range as C. this will automatically exclude A and B as they are not in the same revenue range of C.
one way to do this is first determine the lower and upper range of the Revenue of C and save them in variables:
vLower: =only({<Type={'Revenue'}, Product={'C'}>} if(Data>=Lower and Data<=Upper,Lower))
vUpper: =only({<Type={'Revenue'}, Product={'C'}>} if(Data>=Lower and Data<=Upper,Upper))
then in your chart, you can have the Product as Dimension and the following as the expression:
=sum({<Type={'Revenue'},Product-={'C'}, Data={">=$(vLower)<=$(vUpper)"}>}Data)
this will result in products D and E. if your business rule explicitly says exclude A and B:
=sum({<Type={'Revenue'},Product-={'C','A','B'}, Data={">=$(vLower)<=$(vUpper)"}>}Data)
adding Product to the chart will validate the expression. if you only need the total revenue, just drop the Product dimension
Dear Edwin
Sorry for the late reply as I was sick and thank you so much for your advice and explanation.
Can you advise how to present the answer as per attached format :
Thank you in advance for your help, Tracy