Median/Average per row

Questions & Answers
1

Hi,

i’ve got a report that measures hours per Month using the “time” drill through.

Now i want to calculate an median/Average per Line to see how many Budget is plannable per month.

Actually in use:

CASE WHEN NOT IsEmpty([Measures].[Stunden]) THEN
Median(VisibleRowsSet(), [Measures].[Stunden])
END

(doesn’t work properly)

Definition:

{
“cube_name”: “TMCO”,
“cube_reports”: [ {
“name”: “Übersicht_Stunden_V1”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Buchungen Bezeichnung]","[Measures].[Stunden]","[Measures].[Buchungen Angebotstunden]","[Measures].[Reststunden]"],“members”:[{“depth”:0,“name”:“Stunden”,“full_name”:"[Measures].[Stunden]",“format_string”:"#,##0.00",“drilled_into_dimension”:“Time”}]},{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[],“bookmarked_members”:[]}]},“rows”:{“dimensions”:[{“name”:“Buchungen”,“selected_set”:["[Buchungen].[All Buchungens]"],“members”:[{“depth”:0,“name”:“All Buchungens”,“full_name”:"[Buchungen].[All Buchungens]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true}],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Buchungen”:true},“cell_formatting”:{"[Measures].[Reststunden]":[{“max”:“0”,“background_color”:"#D4153B"},{“min”:“0”,“background_color”:"#006644"}]},“freeze_header”:true}},“calculated_members”:[]}
} ],
“calculated_members”: [{“name”:“Buchungen Bezeichnung”,“dimension”:“Measures”,“formula”:"[Buchungen].CurrentMember.get(‘Bezeichnung’)",“format_string”:"",“annotations”:{“group”:“Custom properties”,“predefined”:false}},{“name”:“Buchungen Angebotstunden”,“dimension”:“Measures”,“formula”:"[Buchungen].CurrentMember.get(‘Angebotstunden’)",“format_string”:"#,##0.00",“annotations”:{“group”:“Custom properties”,“predefined”:false}},{“name”:“Reststunden”,“dimension”:“Measures”,“formula”:"[Measures].[Buchungen Angebotstunden] -\n[Measures].[Stunden]",“format_string”:""}]
}

Could someone please help me on this case?

Thanks in advance!

1 Like
2

Hi,

no one with an idea for this?

3

I understand that you would like to calculate the Median over time for each “Buchungen”.
In that case, the function Median() should contain a set of months over which perform the calculation. The updated expression might look like this:

CASE WHEN NOT IsEmpty([Measures].[Stunden]) THEN
Median(
  --set of all months to calculate Median for each Buchungen on report rows
  [Time].[Month].Members,
  --numeric expression for Median
  [Measures].[Stunden])
END

There are more details on aggregate functions and how to define a set for them: https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures#Calculatedmeasures-Aggregatemeasures

Another option. You might want to set Time dimension on rows and then use standard calculations on measure “Stunden” (Add calculated -> Statistical -> Average or Median).
There are more details on standard calculations: https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure.

Best,
Zane / support@eazyBI.com

1 Like
4

Hi,

thanks, that works so far. Can i “filter” Month except the current Month? When it’s not ended, the median/average is measured incorrect.

5

Hi, any ideas? Thanks in advance

6

Hi @jan-hendrik_woecht,

Yes, you can define the period for the median. In that case, add Filter for the set of months and enter filtering criteria. In this case, you may use function DateBeforePeriodEnd() and check that the first date of each period does not exceed the previous month.

CASE WHEN NOT IsEmpty([Measures].[Stunden]) THEN
  Median(
  --set of months to calculate Median for each Buchungen on report rows
  Filter(
    [Time].[Month].Members,
    --filter only those months before the current month
    DateBeforePeriodEnd(
      [Time].CurrentHierarchyMember.StartDate,
      [Time].[Month].CurrentDateMember.PrevMember)
    ),
  --numeric expression for Median
  [Measures].[Stunden])
END

If you like, you may also define the start and the end of the period using function DateBetween() or DatemembersBetween(). More details on functions to filter dates are here: