How to find the second absolute value with its sign

[Metti]

Hi,

I am trying to find the second maxium absolut value with respect to its dimension. So far I have managed to find its absolute value with below expression but I need to know what is its sign (positive (+) or negative (-)).

Does any one have an idea?

Max(Aggr(fAbs(Measure), firstDimension, secondDimension), 2)

[NitinK7]

Hi

you can try like 

Num(Max(Aggr(Measure, firstDimension, secondDimension), 2),  '#,##0;(#,##0)')

if number is negative(-) then it is show under  bracket like (1235)

if number is positive (+) then it is show without bracket like 1234.

[Metti]

Hi,

Your suggested expression will alwyas give me the second maximum value. I am trying to find the second Maximum absolute value. If I have a table like below, I would like to find -80. If I use your expression I will get 60.

[tresesco]

Try using FirstSortedValue(), like:

=FirstSortedValue(Num,-fabs(Num),2)

[Metti]

I dont think your expression would work. You can try it in this dummy table where -80 should be the output.

[tresesco]

How exactly you are trying to get the output, in KPI object or in the same chart as a new column? Could you post your expected output in picture?

[Metti]

I would like to show it as single value for kpi

In the screenshot that you attached, i don't see any dimension to aggregate on. I need to show the second maximum absolute value with respect to two dimension column.

[Kushal_Chawda · Accepted solution]

may be you have to use Aggr if you want it based on dimensions

=FirstSortedValue(aggr(sum(Measure),Dim1,Dim1),-fabs(aggr(sum(Measure),Dim1,Dim1)),2)

Where Measure,Dim1 & Dim2 are Your actual measure and dimensions respectively

[Metti]

Perfect. This one worked 🙂