Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
The set analysis below, will bring back results for any portfolio where the GBP proce eds is > 500,000 (excluding portfolio 22701282).
I would like to amend it slightly, so that the below is still correct but with the additional criteria that any portfolio with proceeds < 500,000 is also included but they are grouped together and renamed as 'Other'
Sum(Aggr(
If(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'},period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, portfolio -= {22701282}>} [GPR GBP]) * -1, 1E6))
, bussline_reclass, portfolio, portfolio_text))/1000000
For example below currently below I would only return Prisa and SLR. I would like to group the rest to be called 'Other' as they are less than 500,000
Could anyone help with this?
Many thanks
Paul
Accepted Solutions
It had some trouble with the , in the 500,000
Replaced the old dimensions with the new ones:
This calculation is working for me:
=IF( AGGR(
(sum({<entity -= {"0148"},pl -={"X01*"}, proceeds = {"Proceeds"}, period = {">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"},portfolio -= {22701282} > } [GPR GBP])*-1)
, portfolio) >= 500000 , portfolio, 'Others')
you could do the same for portfolio_text and Money Multiple (GBP) Depending on the requirements.
Final result:
Just set Dimension Limits?
Or you could create a calculated dimension? Something like:
IF( AGGR(
//Your measure
, [Dimension Field]) <= 500,000 , [Dimension Field] ,'OTHERS')
... 
