Hi!
I am triying to calculate the Median of an Hour Spent, but i can’t reach it.
I have a report which has 3 Dimensions, Provincia (Example: Logroño), Tipo (Incidente) and Categoria interna. Then i have the Horas Imputadas (Hours Spent) by Categoria Interna, and a Custom Measure Promedio de Horas Imnputadas that is the Average gruped by Categoria Interna.
I need the same measure as the Average but with the Median.
how can i get it?
I used this formula but i only get the Median grouped by Tipo:
Median(
[Categoría interna].[Categoría interna].Members,
[Measures].[Horas Imputadas]
)
Thanks in advance!
Ariel
@ariprado1
Median would be usually calculated using Descendants function which is a very heavy function and would depend on the report context.
You could try the following code, but it can be slow if you have imported many issues in the “issue” dimension.
When selecting it in your report, make sure you enable “Nonempty” cross join for report rows.
MEDIAN(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
Iif(
[Provincia y Sede].currentMember.level.name = "Provincia y Sede",
[Provincia y Sede].currentMember.name=
[Measures].[Issue Provincia y Sede],
1
)
AND
[Measures].[Issues with hours spent]>0
),
CASE WHEN
[Measures].[Hours spent] > 0
THEN
[Measures].[Hours spent]*60
END
)
If that calculation returns an timeout error, then please reach out to eazyBI support with more details about your report (and definition) so perhaps we can guide you through steps to make the report layout simpler by splitting it in some ways, if that is an option for you.
Martins / eazyBI
