KPI chart - rangesum above only show me the day when it hits a number

[ugurarslan]

Dear.

I have created a pivot table with the following data:

Row: [AR day nbr] - gives me the day number of AR 

Collumn: [Inv Year Month]

Measures: 

=rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo()))

Result is:

AR  ay Nbr	Inv  Month	Feb 2017
-13		3.0%
-12		4.3%
-11		5.3%
-10		6.4%
-9		6.8%
-8		8.2%
-7		8.5%
-6		13.3%
-5		17.3%
-4		20.4%
-3		24.3%
-2		28.0%
-1		28.4%
0		63.0%
1		64.6%
2		77.2%
3		81.8%
4		84.4%
5		85.6%
74		98.6%
75		98.6%
76		98.6%
89		98.6%
90		98.7%
91		98.7%
93		98.7%
94		98.7%
95		98.8%
96		98.7%
97		98.7%
98		98.7%
99		98.7%
111		98.9%
112		98.9%
113		98.9%
114		99.0%
115		99.0%
116		99.0%
125		99.1%

Now I want to create a KPI where I can show in the KPI what the the day number is when hit the number >99%. For an example, in the example used above the KPI should show me 114.

Can you please help me?

Should this be an if function?

Thanks a lot.

Ugur

[saminea_ANZ]

Perhaps this?

FirstSortedValue(DISTINCT {<[AR day nbr]={"=(rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo())))>=0.9"}>} [AR day nbr],

-Aggr(rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo())), [AR day nbr]), 1)

[ugurarslan]

Thanks a lot

I looks my " doesn't work properly. It grates out all.

[Channa]

MIN({< [AR day nbr]={"=rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo()))>0.9"}>}  [AR day nbr])

[saminea_ANZ]

You are using in KPI, Right? Can you please post sample file to test?

[ugurarslan]

Hi Channa,

Thanks

Is this correct? the result shows me the first AR day and not when it hits 99%.

[ugurarslan]

Yes I am,

When I write this:

I get as a result -.

[saminea_ANZ]

I always preferred to use FirstSortedValue to get move consistent. Can you try this way?

MIN({< [AR day nbr]={"=Num(rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo())), '#,#0%')>0.9"}>}  [AR day nbr])

[ugurarslan]

sorry didn't work also..

[sunny_talwar · Accepted solution]

How about this

Min({<[AR day nbr] = {"=Aggr(RangeSum(Above(Sum({<[Type] = {'APP'}, [Cash App Type] = {'CASH'}>} [AR USD]*(-1))/Sum({<[Type] = {'INV'}, [Rebill Indicator] = {'Non Rebill'}>} TOTAL <[AR Year Month]> [AR USD]), 0, RowNo())), [AR Year Month], ([AR day nbr], (NUMERIC))) >= 0.99"}>} [AR day nbr])