I wonder if it's possible to create a table like this:
I have calculated the equity ratio for two companies shown in the column "Calculated field 1". Now I would like to create the average and the minimum value of this column for each company! as shown in the table (red numbers).
For clarification, the column C, row C5 to C26 show the average for company 1. The row C28 to C49 show the average for company 2.
Any ideas how to proceed this?
Average:=
AVERAGEX(
ALL(Datasrc[Year])
,[Calculated field 1] // Seriously? Come up with a better name.
)
Minimum:=
MINX(
ALL(Datasrc[Year])
,[Calculated field 1]
)
*X() functions take a table expression as their first argument. They step row by row through this table, evaluating the expression in the second argument for each row, and accumulate it. AVERAGEX() accumulates with an average, MINX() with a minimum.
ALL() returns unique elements of a table, column, or set of columns stripped of context. Since we are only calling ALL() on the [Year] column, that is the only column whose context we remove. The context from [Stock Ticker] remains in place.
These two measures are unreasonable in the grand total level.
Ninja edit: There's likely a better way to write the measures, but with no insight into what [Calculated field 1] is doing, I can't make suggestions toward that. *X() functions force single threaded evaluation in the formula engine. It's not a bad thing, but you take a performance hit over queries which can utilize only the storage engine. If you could rewrite it to a CALCULATE(SUM(),ALL(Datasrc[Year])) / ALL(Datasrc[Year]), you'd get faster evaluation for sure, but I have no clue what your data looks like.
