Please see the file I have shared in the following link:
https://drive.google.com/file/d/1q7FE85qHoB_OhAm4hlmFidh6WP3m-UnK/view?usp=sharing
I have a dataset with the value of various items on different dates. I first need to calculate ratio by dividing the value of an item on a particular date with the earliest value in the chosen date filters. I then need to show the per day ratio of all items (columns I & J) as the desired output.
I am able to get the "first value" in the filtered table by using the following formula:
first_cr =
VAR item = MAX('Table 1'[item])
VAR min_updated = CALCULATE(
MIN('Table 1'[last_updated]),
'Table 1'[item] = item,
ALLSELECTED('Table 1')
)
RETURN
CALCULATE(
AVERAGE('Table 1'[cr]),
'Table 1'[last_updated] = min_updated,
'Table 1'[item] = item,
ALLSELECTED('Table 1'[fk])
)
However, I am unable to figure out how to calculate the average of all the individual item ratios at just the date level. I guess I need to use AVERAGEX somehow, but not sure how. Perhaps my first_cr formula could use more refinement.
I'd appreciate any help or guidance in the right direction. Thanks for your time.