Group data based on calculation results

[mhmhmh91]

Hi All,

I have table like below.

Current Value	Target Value	(Current - Target) Delta
100	50	50
120	60	60
77	80	-3
62	22	40
70	100	-30
50	51	-1

I want to show a total of (Current - Target) Delta <0 and (Current - Target) Delta >= 0 in KPI objects. How to do that?

Thank you,

Mandi

[aarkay29]

Based on what you provided

may be this

Sum(Aggr(only({<[Current Value]={"=[Current Value]>[Target Value]}>}[Current Value]-[Target Value]),[Current Value],[Target Value]))      for '>0'

Sum(Aggr(only({<[Current Value]={"=[Current Value]<[Target Value]"}>}[Current Value]-[Target Value]),[Current Value],F2))      for '<0'

But there should be a proper ID to aggregate instead using values

If you have a key then use this

Sum(Aggr(only({<ID={"=[Current Value]>[Target Value]"}>}[Current Value]-[Target Value]),ID))

[sunny_talwar · Accepted solution]

May be this

>= 0

Sum(RangeMax(([Current Value] - [Target Value]), 0))

and

< 0

Sum(RangeMin(([Current Value] - [Target Value]), 0))

[sunny_talwar]

Sample attached

[mhmhmh91]

It seems the result is not correct. Thank you all the same!

[mhmhmh91]

It solves my problem. Thank you very much!