Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))