Set analysis and hide row and ignore selection

[chenriot]

Hi,

I have some products that are sold by reps that are attached to a regional director (DR).

I create a straight table with Product in dimension and 3 expressions :

- Revenue : it's the sum of the revenue (=sum(Revenue) )

- Revenue National : it's the sum of the revenue by ignoring the selections of the reps and the DR (=sum({<Rep=,DR=>} Revenue) )

- Revenue DR : it's the sum of the revenue for the DR selected or for the DR attached to the rep selected. (=sum({<Rep=>} Revenue) )

I have 2 problems when I select one rep :

- all products are displayed : I would like only the products sold by the selected rep

- the "revenue DR" has the same result that "Revenue National" if I don't select the DR.

For the joined example for the rep1, I would like one row for P1 with

revenue=10

revenue national = 100

revenue DR1 = 25

How can I do this ?

Thank you in advance for your help

[sunny_talwar · Accepted solution]

Try these expressions:

1) =If(Sum(Revenue) > 0, Sum(Revenue))

2) =If(Sum(Revenue) > 0, Sum({<Rep=,DR=>} Revenue))

3) =If(Sum(Revenue) > 0, Sum({<Rep=, DR = P(DR)>} Revenue))

[chenriot]

Thanks a lot for your help.

It works very well.

Bye