cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattyicee
Frequent Visitor

Set Goal based on Median Value of a Measure

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!

 

Mattyicee_0-1610037212599.png

 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@Mattyicee 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

5 REPLIES 5
parry2k
Super User III
Super User III

@Mattyicee 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

That was the key - Thanks!

ToddChitt
Post Prodigy
Post Prodigy

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
Super User III
Super User III

@Mattyicee 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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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]
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors