Ignoring Selections - Median

[#1]

Hi everyone,

I have a very complicated expression that I am using to generate a median line on my chart. I have a requirement to make sure the median does not change when values in Market dimension are selected. How/where do I specify in the expression to ignore selections in that dimension?

=Median( TOTAL

aggr(

(3-(3*((aggr(rank([Est Market Size],2),MarketID))/Count(TOTAL(MarketID)))))*avg({<Factor={'Market Size'}>}Weightings)

+

(3-(3*((aggr(rank([Est Market Growth],2),MarketID))/Count(TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}>}Weightings)

+

([OfferingMarket Fit]*avg({<Factor={'Market Fit'}>}Weightings))

+

([Type of Market]*avg({<Factor={'Type of Market'}>}Weightings))

+

([Customer Requirements]*avg({<Factor={'Customer Requirements'}>}Weightings))

+

([Competitive Dynamics]*avg({<Factor={'Competitive Dynamics'}>}Weightings))

,[Tier 4 - Market Segment]))

It is not an option to pre-calculate the median and write it as a value.

Thanks!

[sunny_talwar]

Try this may be:

=Median({<Market = >} TOTAL

aggr(

(3-(3*((aggr(rank(Only({<Market = >}[Est Market Size]),2),MarketID))/Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Size'}, Market = >}Weightings)

+

(3-(3*((aggr(rank(Only({<Market = >}[Est Market Growth]),2),MarketID))/Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}, Market = >}Weightings)

+

(Only({<Market = >}[OfferingMarket Fit])*avg({<Factor={'Market Fit'}, Market = >}Weightings))

+

(Only({<Market = >}[Type of Market])*avg({<Factor={'Type of Market'}, Market = >}Weightings))

+

(Only({<Market = >}[Customer Requirements])*avg({<Factor={'Customer Requirements'}, Market = >}Weightings))

+

(Only({<Market = >}[Competitive Dynamics])*avg({<Factor={'Competitive Dynamics'}, Market = >}Weightings))

,[Tier 4 - Market Segment]))

Everything in red is the new stuff. I hope nothing is broken and hopefully it will work.

Best,

Sunny

[#3]

Thank you for your reply Sunindia. Unfortunately, your solution didn't work when I made a selection, although the number seemed to move less.

Your solution seemed to cover everything, I don't understand why it doesn't work!

[sunny_talwar]

I might have missed something. I can look again right now. Let me see and get back to you.

Best,

Sunny

[sunny_talwar]

Check this one out:

=Median({<Market = >} TOTAL Aggr(

(3-(3*((

Only({<Market = >} Aggr(Rank(Only({<Market = >}[Est Market Size]), 2), MarketID)))/

Count({<Market = >}TOTAL(MarketID))))) * Avg({<Factor={'Market Size'}, Market = >}Weightings)

+

(3-(3*((

Only({<Market = >} Aggr(rank(Only({<Market = >}[Est Market Growth]),2),MarketID)))/

Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}, Market = >}Weightings)

+

(Only({<Market = >}[OfferingMarket Fit]) * Avg({<Factor={'Market Fit'}, Market = >}Weightings))

+

(Only({<Market = >}[Type of Market]) * Avg({<Factor={'Type of Market'}, Market = >}Weightings))

+

(Only({<Market = >}[Customer Requirements]) * Avg({<Factor={'Customer Requirements'}, Market =>}Weightings))

+

(Only({<Market = >}[Competitive Dynamics]) * Avg({<Factor={'Competitive Dynamics'}, Market =>}Weightings))

,[Tier 4 - Market Segment]))

Best,

Sunny

[#6]

Hi Sun,

That didn't work either. I think that this is because I am fundamentally not aggregating correctly.

In the original formula, I am calculating 6 different scores for each market and adding them together. That formula only works when I only have one Market selected.

The real question is, how do I write a formula to calculate the scores for each Market, even with no selections?

Anyway, I will have to return to this later step-by-step. Thank you for your time!

[sunny_talwar]

I may be help if you can share a sample with your expected output.

Best,

Sunny

[#8]

I completely agree! However, I am facing some deadlines right now - when I come back to this, I will post some sample data in an app for you. Thanks!

[sunny_talwar]

Works for me

Best,

Sunny