Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have column 1 and column 2 and I want to find the share of column 1 against its total. (see picture below)
In column two I have done a formula that aggregate the numbers and show the total
The problem is that I want to avoid the number to change when I am filtering by a dimension (IE Investor Type) as I want always to show the share against the total.
This is the formula for the denominator which sits in column 2 at the moment:
aggr(nodistinct sum(aggr(if($(vEligibleComm)>ConcentrationLimit,
Sum({<[Investor Type]-={"Ineligible"}>} Unfunded)
-
($(vEligibleComm)-ConcentrationLimit)*aggr(Nodistinct Sum({1<[Investor Type]-={"Ineligible"}>} Total <Fundgroup>Unfunded),Fundgroup),Sum({<[Investor Type]-={"Ineligible"}>} Unfunded)),Investor)),Fundgroup)
This is also the Variable $(vEligibleComm):
Sum({<[Investor Type]-={"Ineligible"}>} Unfunded)/aggr(Nodistinct Sum({1<[Investor Type]-={"Ineligible"}>} Total <Fundgroup>Unfunded),Fundgroup)
Let me know your thoughts...
@mikecherry Try this
Only({<[Investor Type]>} Aggr(nodistinct Sum({<[Investor Type]>} Aggr(If($(vEligibleComm) > Only({<[Investor Type]>} ConcentrationLimit),
Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}>})>} Unfunded)
-
($(vEligibleComm)-Only({<[Investor Type]>} ConcentrationLimit))*Only({<[Investor Type]>} Aggr(Nodistinct Sum({1<[Investor Type] = e({<[Investor Type] = {"Ineligible"}>})>} Total <Fundgroup>Unfunded),Fundgroup)), Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}>})>} Unfunded)),Investor)),Fundgroup))
and this for the variable
Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}>})>} Unfunded)/Only({<[Investor Type]>} Aggr(Nodistinct Sum({1<[Investor Type] = e({<[Investor Type] = {"Ineligible"}>})>} Total <Fundgroup>Unfunded),Fundgroup))
I might have misplaced a parenthesis, but the idea is to ignore selection in [Investor Type] with all possible aggregations.... including naked Aggr() functions using Only() function
Sunny ... you are a bloody hero !!!!! It seems to work, I will back-test it little bit more and let you know
Thank you so much !!!!!!!
What if there's another selection I need to avoid ? (IE Investor)
Try this @mikecherry
Only({<[Investor Type], [IE Investor]>} Aggr(nodistinct Sum({<[Investor Type], [IE Investor]>} Aggr(If($(vEligibleComm) > Only({<[Investor Type], [IE Investor]>} ConcentrationLimit),
Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}, [IE Investor]>}), [IE Investor]>} Unfunded)
-
($(vEligibleComm)-Only({<[Investor Type], [IE Investor]>} ConcentrationLimit))*Only({<[Investor Type], [IE Investor]>} Aggr(Nodistinct Sum({1<[Investor Type] = e({<[Investor Type] = {"Ineligible"}, [IE Investor]>}), [IE Investor]>} Total <Fundgroup>Unfunded),Fundgroup)), Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}, [IE Investor]>}), [IE Investor]>} Unfunded)),Investor)),Fundgroup))
and variable
Sum({<[Investor Type] = e({<[Investor Type] = {"Ineligible"}, [IE Investor]>}), [IE Investor]>} Unfunded)/Only({<[Investor Type], [IE Investor]>} Aggr(Nodistinct Sum({1<[Investor Type] = e({<[Investor Type] = {"Ineligible"}, [IE Investor]>}), [IE Investor]>} Total <Fundgroup>Unfunded),Fundgroup))