Aggregation to different levels

[#1]

Hi

I a sheet in my QV that has several dimensions and three measures.  I want to be able to aggregate two of the measures to a set of dimensions and the one measure to another set.  So for example I have dimensions

A

B

C

D

E

F

and measures 1,2 3. 

I want measure 1 to work across all dimensions and measures 2 and 3 to work across only A, B, C.  So if lets say the user selects something from dimension F measures 2 and 3 should not aggregate to to that level.

[morganaaron]

If you use the total qualifier but exclude the dimensions you want it to calculate over, it should work, so:

=Sum(TOTAL <'A', 'B', 'C'> 2) to sum measure 2 for example.

[#3]

so I have the following as a variable currenty SUM({<[P&L Classification]={Income}, RecordType={SLR}>}[Actual]).  So based on your example do i need to extend this so that the dims A, B and C are included?

[morganaaron]

Yes - if you're already using set analysis, there's an easier way to do it, if you include:

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=>}Actual)

That last "F=" will mean it ignores any selections made in the dimension F. You can keep adding those so "F=, E=, D=' and it will ignore any selections over those dimensions.

SUM({<[P&L Classification]={Income}, RecordType={SLR}>}[Actual]).

[#5]

so in your example do you mean like this

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=>, E=>, D=>}Actual}

[#6]

also when i have F etc should this be the name of the attribute? 

[morganaaron]

That's correct, assuming F, E and D are the names of dimensions that you don't want the figures to be affected by.

[ashwanin]

If you want to ignore the Dimensions F,D & E then its better not to include them in Set analysis.

This final expression should be like this :

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}>}Actual)

[morganaaron]

I think he said he doesn't want a selection in a Dimension F for example to affect the figure, which it would do unless it's put in a total statement or removed from the set in a set analysis statement.

[jagan]

Hi,

Try like this

Sum({<[P&L Classification]={'Income'}, RecordType={'SLR'}, F=, E=, D=>} TOTAL <A, B, C>Actual}

In the above expression, D, E F selections are not consider and A,B,C dimensions are not considered.

Hope this helps you.

Regards,

jagan.