Set Goal based on Median Value of a Measure

[Anonymous]

I am trying to create a goal for a set of users that is based on the median of the total transactions done per user. I was able to create this based on the medianx and summarize function but when I break it down by user in a table it gives me the median for that row instead of the same number for all users. I want it to display the same for all users so I can end up dividing the total transaction by the Goal amount to get the % per user. Essentially I would like to show 14 for all rows of the Goal column instead of the median of that row. Any thoughts on how to accomplish this?

Thanks!

[parry2k · Accepted solution]

@Anonymous try this

Goal = MEDIANX(
SUMMARIZE(ALL('Closed Cases'),'Closed Cases'[Case Owner],
"MEdianValue",[Transactions per Rep]),
[MEdianValue])

or replace ALL with ALLSELECTED if you want to keep the filter context.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

That was the key - Thanks!

[ToddChitt]

Sounds like you need to add the ALL( ) function to your measure calculation. 

For example, the following 'airware' function will get the sum of the metric, but be sliced by all dimensions:

MySum = SUM ( [Some Metric] )

But THIS will show the sum for all Users, even if we slice by user:

My Sum for Users = CALCULATE ( SUM ( [Some Metric] ), ALL ( 'Users' ) )

It will still repsect slices by Date, or other dimensions, but will not respect slices by User.

Can you share your DAX code so we can help fuuther?

[parry2k]

@Anonymous you need to divide by using ALL in your function so that it removes any filter applied on the measure. Share the actual measure (don't use the image) but paste the code and will provide you the new measure.

[Anonymous]

@parry2k Thank you for the response!

Here is the Formula for the Goal 

Goal = MEDIANX(

SUMMARIZE('Closed Cases','Closed Cases'[Case Owner],

"MEdianValue",[Transactions per Rep]),

[MEdianValue])

% of Goal = 

[Transactions per Rep]/[Goal]