Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.