Medians and context issues in DAX

[srizzo123]

I am having an issue that I am hoping some more experienced DAX programmers may be able to help me with. This problem is cross-posted on StackOverflow here (excel - Medians and slicers in DAX - Stack Overflow) with no answers so far.

I have been trying to develop a dashboard in Excel 2013 / PowerPivot / PowerView and one of the graphics I would like to display is a line chart of median performance by hour of day. I would then like to filter the data set with my performance metrics based on a separate field, and link that to a slicer. The medians should be calculated relative to the filtered data set. For the median calculation I am trying to adapt the formula proposed by Marco Russo here (SQLBI - Marco Russo : Median calculation in DAX).

To illustrate the problem, suppose that I have two tables - main_table and other_table. Main_table has 4 fields - RowID, hour_of_day, performance_metric, and category. Other_table has two fields - hour_of_day and median_field. My goal is to find a formula for median_field such that it shows the median performance metric by hour of day, but can still be sliced by category. The formula I tried to use for the medians was

<code>=CALCULATE(MINX(FILTER(VALUES(main_table[performance_metric]), CALCULATE(COUNTA(main_table[performance_metric]), main_table[performance_metric] <= EARLIER(main_table[performance_metric])) > COUNTA(main_table[performance_metric]/2), main_table[performance_metric]), FILTER(main_table, main_table[hour_of_day] = EARLIER(other_table[hour_of_day]))) </code>

However, when I create a slicer based on category in main_table, my chart does not seem affected by the slicer. My understanding was that by putting main_table as opposed to ALL(main_table) as the first argument in the last FILTER call, my median calculations would be subject to slices and filters applied to main_table. Am I missing something obvious here?

 

[marco.russo]

I took a look at the formula, but it's not clear to me whether it's a measure (calculated field) or a calculated column. If it's in a measure (calculated field) it's not clear to me what you are doing with "other table" in the EARLIER statement. If you can post a sample repro workbook it would be simpler to take a look at the formula.

 

[srizzo123]

Sorry - I tend to mix up calculated fields and calculated columns. The formula is for the calculated column median_field in other_table. I will update the original post with a link to a sample file.

 

[srizzo123]

I apologize for double posting, but I seem to be unable to edit my own posts for some reason (mods - please feel free to merge this into the post above). Anyways, the download link for the sample file is

median_and_context_issue.xlsx - Speedy Share - upload your files here

I can upload it elsewhere if that would be more convenient.

 

[marco.russo]

You cannot use a calculated column in this case. Calculated columns are computed at process time and store the result into the Power Pivot table *before* you do any query.
If you want your calculation to be affected by the slicer, you have to create a measure (aka calculated field in Excel 2013).
You should do this in two steps:
1) Create the relationship between the two tables using the HOD column
2) Create the Median calculated field using the following DAX code:

Median :=
MINX (
    FILTER (
        VALUES ( main_table[Performance_metric] ),
        CALCULATE (
            COUNTROWS ( main_table )
            main_table[Performance_metric]
                <= EARLIER ( main_table[Performance_metric] )
        )
            > COUNTROWS ( main_table ) / 2
    ),
    main_table[Performance_metric]
)

Let me know if this works for you.

 

[srizzo123]

That does work - the measure changes when I adjust the slicer. Unfortunately I will need a lot of measures - in the actual application I want to plot the medians of at least 5 different performance metrics by hour of day (that can still be sliced by other categories) - but if calculated columns are pre-computed then measures are really my only option. Thanks for the help!

 

[Tom_Poland]

Hello Guys!

I need to create a pivot with median (MS Office Pro 2010). I tried to define a measure with code indicated above, however I get a pop up, that syntax of calculate is incorrect. Could you please help me? I haven't changed anything in the code but table and column names.

 

[Tom_Poland]

Anyone..?