Average and Median for the Dimension Value in a Frequency Table
1. Given the following frequency table:
How to calculate the Average and the Median of the Value? Is this possible at all?
In Excel at least the average could be calculated via SUMPRODUCT(Value, Frequency) / Sum(Frequency) = 100 / 15 = 6.67
2. If you transform the table into an ordered list like this:
How to calculate the Average and the Median of the Value? Is this possible at all?
Would it be possible if the values were a measure?
In Excel you could easily calculate this via MEDIAN(Value) and AVERAGE(Value).
3 replies
-
If you simulate your example, and load the data with a granular key, you can achieve the outcomes you want, shown below using the built-in totals. This will work for both averages and median.
if you remove the key from the dataset or the query, you can derive the average easily as well. But you CANNOT get the median. Like your second example, you need the grain of data for the formulation to return a median, at that given grain. You cannot derive the median without it - a simple mathematical truth.
-
Hi
Also check out this Q&A from a while ago on replicating the EXCEL SUMPRODUCT function in Pyramid.
Ian
-
Thank you both, and !
So, in the end it is crucial to have the dimensional value (ObeeOne's "item" or my "Value") as a measure - and of course to have a list with the granular data. I'll give it a try!
Best regards,
Michael