Specific ratio

[User93]

Hi all,

I would like to make the following calculation :

First, for a COUNT(Measure), get for a dimension the value with the highest result.

Second, calculate the ratio between the COUNT(Measure) for this dimension value, and the global COUNT(Measure).

I succeeded for the first part, with this : firstsortedvalue(Dimension,-Aggr(COUNT(Measure),Dimension))

Problem is, when I try to use this for the ratio, I get an error :

COUNT({<Dimension ={firstsortedvalue(Dimension,-Aggr(COUNT(Measure),Dimension))}>}Measure)
/COUNT(Measure)

Can you help me on this one ?

Best regards.

[User93]

Hi,

I actually found how to obtain the highest COUNT with this formula, I needed to use the "upper" Dimension for which I wanted to calculate the ratio, as well as the "lower" dimension for which each COUNT is calculated :

COUNT(
{<
[UpperDimension] ={"=Aggr(Rank(COUNT([Measure])),[LowerDimension]) = 1"}
>}
Measure)

With the ratio being :

COUNT({<[UpperDimension] ={"=Aggr(Rank(COUNT([Measure])),[LowerDimension]) = 1"}>}Measure) / COUNT(Measure)

However, the problem with this formula is that if I want to obtain the lowest COUNT, I have to change the rank to the right number (2 if only two rows).

Is there a way to modify the formula to always obtain the lowest COUNT by LowerDimension, without having to put a number that can be wrong if there are more rows than anticipated ?

Best regards.

[User93 · Accepted solution]

Hi,

In the end, I found an easier solution for the ratio :

Min(Aggr(COUNT([Measure]),[LowerDimension]) )
/COUNT(Measure)

It works if displayed in a table with the UpperDimension.

Best regards.