Sum based on maximum value of flag

[spinchuk]

Hi guys,

I have data like this:

Estimate	Version	Amount
a	1	123
a	2	136
b	1	52

I need help with formula that will calculate "Latest" column in the PIVOT table like below:

Estimate	Original	Latest
a	123	136
b	52	52

My current expression for "Original" is Sum({$<Version = {1}> Amount} work fine.

But my Expression for "Latest" Sum({$<Version = {$(=Max(Version))}>}Amount) does not work, because the Max(Version) = 2 for both estimates. Since estimate b does not have version 2 - i'm getting blank.

Any help please?

Thank you

[#2 · Accepted solution]

Attached with answer.Hope it helps

Thanks

[spinchuk]

Ajay, thank you, that works.

However my sub total for each category stopped working...

My actual results looks like this:

Estimate	Category	Original	Latest
a	cat1	123	136
	cat2	2	7
	cat3	3	8
	Total	128	151
b	cat1	52	52
	cat2	5	5
	Total	57	57

When I implemented FirstSortedValue(), the numbers in "Latest" column are correct, but Total now "-".

Thank you

[#4]

For subtotals use dimensionality () and kinda play arund with the formyula