How to calculate ratio of a value for a group compared to the group with the Max value

[#1]

In the attached data QVW, I have three groups, each of which has a cost. I'd like to evaluate the % of cost of each group relative to the group that has the max cost.

For example, if the group with the largest cost is, say, $150, I want to compare other groups to this. If another group has a total cost of $75, I want an expression that will comapre these values and return 50%.

I thought that using this expression would give me the desired results:

SUM(Cost)

/

MAX( AGGR( SUM(Cost), Group))

However, I see the denominator MAX( AGGR( SUM(Cost), Group)) is not giving me the Max Cost among all the groups.

What expression would I use here? Is this a job for set analysis?

Thanks for your help!

[whiteline · Accepted solution]

Hi.

For your simple sample you can just use total qualifier:

=SUM(Cost)/Max(total Cost)

In complex cases with aggr you should also use total qualifier (in some cases with restrictions <>):

=SUM(Cost)/MAX(total AGGR( SUM(Cost), Group))

[#3]

Thanks, adding the TOTAL qualifier inside the MAX fucntion worked.

I was a little confused about about when exactly the TOTAL qualifier disregards dimensions.

It appears that the TOTAL qualifier disregards dimensions unless aggregation is explicitly used in the expression (as was done with the AGGR function).

I'm curious, though, why this wouldn't also work: MAX(TOTAL SUM( TOTAL <Group> Cost)). Doesn't the <Group> I've added in the SUM also do aggregation?

[whiteline]

SUM( TOTAL <Group> Cost))

means that you want calculate SUM(Cost) for each Group discarding all other dimensions (except Group).

[#5]

Got that. I guess I'm struggling with why wrapping that expression with MAX( TOTAL) wouldn't return the max cost among all those groups.