Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

That was the key - Thanks!

ToddChitt
Super User
Super User

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

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.