Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
I might have missed something. I can look again right now. Let me see and get back to you.
Best,
Sunny
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
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!
I may be help if you can share a sample with your expected output.
Best,
Sunny
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!
Works for me
Best,
Sunny