Fairly simple median issue

[hyndmanevanp]

I have a simple table with customer name, employee name, date, hours, and location.  I want to find the median of the hours (displayed by the week, I have a weekending column in my date table) for both the client and employee.  The table is populated by shifts, so it needs summed first.  I have a Matrix, and I can easily see this data there, but I'd like it as a measure to compare it in a column chart vs average.  I've tried measures and duplicating the table in power query, but I get the same answer.  I thought by duplicating the original table twice, naming one customer and one employee, then removing the employee column from the customer table and vise versa, I could use the sum of Hours to find the median.  Regardless, my measure and table attempt both appear to simply pull the median value by location without summing by customer or employee.

Thank you in advance.

[v-xiaotang]

Hi @hyndmanevanp 

Thanks for reaching out to us.

>> Regardless, my measure and table attempt both appear to simply pull the median value by location without summing by customer or employee.

You can try this solution shared by Zubair_Muhammad, Solved: Calculate the median of diferent sum of products o... - Microsoft Power BI Community

Median =
MEDIANX (
    CALCULATETABLE (
        VALUES ( Table1[PRODUCT ID] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Customer] = SELECTEDVALUE ( Table1[Customer] )
        )
    ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

in this measure, the code in the bold font will make measure calculate the median based on the customer

...

FILTER (
ALLSELECTED ( Table1 ),
Table1[Customer] = SELECTEDVALUE ( Table1[Customer] )
)

...

if you want the measure calculate the median based on customer and employee, just try

...

FILTER (
ALLSELECTED ( Table1 ),
Table1[Customer] = SELECTEDVALUE ( Table1[Customer] ) && Table1[employee] = SELECTEDVALUE ( Table1[employee] )
)

...

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[hyndmanevanp]

Thanks.  I think this is close as I'm not getting a formula error, but when I add the measure to the graph, it is blank.  Here is my formula using my table and columns names.  They only issue I see (bold) is your use of [Product ID], I assumed it should be my value column (actual hours), but I've also tried customer and left out columns.  

1 Client Median = MEDIANX (

                                   CALCULATETABLE (

                                               VALUES ( 'Office Comparison'[Actual Hours] ),

                                               FILTER (

                                                        ALLSELECTED ( 'Office Comparison' ),

                                                        'Office Comparison'[Customer] = SELECTED VALUE ( 'Office                                                                         Comparison'[Customer] )

                                                 )

                                 ),

                                 CALCULATE ( SUM ( 'Office Comparison'[Actual Hours] ) )

)

[v-xiaotang]

Hi @hyndmanevanp 

Thanks for your reply.

Have you find the solution now?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[hyndmanevanp]

No, I have not found a solution.