FirstSortedValue returning wrong values

[Coloful_Black]

Hi Masters,

Struggling on FirstSortedvalue function. I want to display KPI for No.1 to No.3 sales by FirstSortedValue fucntion.

Oddly, I got it correctly for No.1. You could tell from the KPI screenshot and the table with red arrow.

But somehow, just could not get no.2 and no.3 correctly. Based on the table, No.2 should be $16.5 M while KPI returns me $7.8 M. 

here is my expression for No.2

FirstSortedValue
([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)],
-Aggr(Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)]),[(MAT).Substance Grp]),2)

Please advise.

Appreciate it

[sunny_talwar]

May be try with simple Max() function

Max(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]))

Max(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]), 2)

Max(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]), 3)

[Coloful_Black]

Hi Sunny_talwar

Thank you so much.

It is working ! Although I still have this complex on why FirstSortedValue fails to work on value...

Cant resist the beauty of simplicity.

A follow-up question. I want to show the label of top 3 and least 3 ranks ..while #1 #2 #3 are always easy... simply put 1 ,2 and 3....

but Is there a simple/innovative way to display the last three? conditional that every time data set would have different count of  subjects ....

subject dimension is 

(MAT).Substance Grp])

thanks

[sunny_talwar]

Using Min() function?

Min(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]))

Min(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]), 2)

Min(Aggr(
  Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)])
, [(MAT).Substance Grp]), 3)

[Coloful_Black]

Hi Sunny_talwar,

I may not make it clear....I want to display the last rank postilion number.  like among all 76 subjects, the last one shows 76 and second last one shows 75.

anyway?

[sunny_talwar · Accepted solution]

May be try this

Max(Aggr(
  Rank(Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)]))
, [(MAT).Substance Grp]))

Max(Aggr(
  Rank(Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)]))
, [(MAT).Substance Grp]), 2)

Max(Aggr(
  Rank(Sum([Sales in USD (MAT CY)]-[Sales in USD (MAT PY)]))
, [(MAT).Substance Grp]), 3)

[Coloful_Black]

appreciate your help....yeah....why I did not think of using MAX + Rank.....