Selecting max / min values

[#1]

Hi guys,

I need to extract the max and the min values using the following expression:

sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))))

The expression is used to show profit margin for a product family. How do I find out what is the highest and the lowest margin? I need this to be the expression in the input box as I'll use the number somewhere else.

Thanks.

[Nicole-Smith]

You probably need something like:

max(aggr(sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))), YourDimensions))

and

min(aggr(sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))-(sum({$<[P&L Category] = {'Cost'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)]))) / if(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))<0,-1*(((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))),((sum({$<[P&L Category] = {'Revenue'}>}[Indirect Reversed (£'000)]+[Direct Reversed (£'000)])))), YourDimensions))