hi guys, I wanted to have the average of the “Time in status” column, but grouped by the “demanding management” line
Does anyone know how I can do it?
The standard calculations consider all values on report rows (documentation on Add standard calculations based on a selected measure). If you would like to group the median calculation by the first column “Gerencia Demandante,” then you should write a new calculated measure using functions:
- Median() to calculate the median over the set of issues.
- VisibleRowset() to get a set of “Gerencia Demandante” and “Issue” combinations in this report.
- Generate() to generate the set of issue associated with each “Gerencia Demandante”.
The expression might look like this:
Median(
--set of all visible rows from the second dimension
Generate(
VisibleRowsSet(),
CurrentTuple(VisibleRowsSet()).Item(1)
),
--values for the same member in the first column
[Measures].[Time in status]
)
This calculation is a bit abstract as it does not care about the first dimension name (in your case, “Gerencia Demandante”). However, it could be applied to other use cases when you want to get the median for each option in the first dimension on rows and calculate it across the members in the second dimension on rows.
To make it work correctly, select the Issue level for the “Issue” dimension and remove the Project level. The aggregated results in some report rows would affect the calcauted result.
You can watch the presentation for more use cases and applications of the VisibleRowSet() function: Flexible MDX formulas: How to Use VisibleRows and VisibleColumns.
Best,
Zane / support@eazyBI.com.