How to show percentage share not considering the filter

[mikecherry]

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...

[sunny_talwar]

@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

[mikecherry]

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 !!!!!!!

[mikecherry]

What if there's another selection I need to avoid ? (IE Investor) 

[sunny_talwar]

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))