Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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
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)
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?
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)
appreciate your help....yeah....why I did not think of using MAX + Rank.....