Median measures

Groundhog

Hi Vidas,

The portfolio slicer is wonderful, so useful and easy to use. I'm finding it a godsend.

There are a couple of new measures which would really help me prioritise new purchases but I’m struggling so wondered if you could help please. One is the median of Cost Basis and the other is median of Total Value s shown on the Holdings tab. I’ve tried (and failed) to do this as I get a repeat of the value of the symbol not the median of all the items shown on the column.
I’m on Excel 2016 so have the function available, but can’t s get it to work on the Cost Basis or total value measure.

Any help you could give would be brilliant

VidasMatelis

Hi,

Can you please clarify what do you mean by "median of Cost Basis" and "median of Total Value". Would that median be calculated using all symbols? or just median for symbol for selected period?

Groundhog

Hi Vidas, thank you for the quick reply. It is the median for all of the symbols please. So on the holdings tab on the spreadsheet the median would be on all symbols, therefore if I were to put a median measure in the pivot I would expect to see the same value against every symbol. The value would then change as different items are selected in the slicer.

I hope this is clear

Groundhog

VidasMatelis

Hi,

Formula would be:

=CALCULATE(MEDIANX(Symbol, Report[Total Value]), FILTER(ALL(Symbol), Report[Total Value]>0))

This formula ignores Symbol table column selection when calculating median, but applies all other table selection.

For Cost basis just replace Report[Total Value] with Report[Cost Basis]

Groundhog

Perfect! Thank you Vidas, you're a star, it works like a dream. I was getting confused on the Filter element. Thanks again