cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV Established Member
Established Member

Median Comparison

Hi,

 

I have some revenue datas for several projects :

 

Capture.PNG

 

In my report, I have a slicer which filter datas by project, and I want a card showing if the median of the selected project is above or under the global median (median of all the projects).

My probleme is that I don't know how to calculate the global median and the median of the selected project in the same measure, because datas are filtered by the slicer.

 

Thanks for your help.

 

Mohan V

 

 

 

3 REPLIES 3
Super User
Super User

Re: Median Comparison

Hey,

 

maybe this is what your are looking for

 

create a measure

median comparison =

DIVIDE(
calculate(median('yourtable'[Revenue]))

/ calculate(median('yourtable'[Revenue]), All('yourtable'[Project]))
,BLANK())

 

The ALL() function in the second calculate removes the selection from the slicer.

 

Hope this helps

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
MohanV Established Member
Established Member

Re: Median Comparison

Thanks for the reply @TomMartens.

 

I have tried what you have suggested but no luck.

it giving me a blank value whether i choose the pro id or not.

 

I ll give you a detailed explanation of what i am trying to do.

 

I have a table as above image. let say each project of different person.

i have created RLS on it. so that who ever logins to their report they can see their respective projects only at service level.

 

this is at powerbi desktop level.

here im trying to calculate the median of all projects, that is nothing but the global median. no filteration, nothing.

 

the image we have shown above is a duplicate of the actual table.

we took the duplicate of actual table and removed the rest of columns except the pro id and revenue.

now i didnt created any relation with the main table.

so that when i calculate median i can get the whole median, that is global median as per my understanding.

 

now in my actual main table if i calculte the median i ll get the median as per filteration of pro ids.

now i can compare this median with the global median. whether the values are high or  not.

 

But as i said i have created RLS in report.

so when i publish this report to service level, who ever logins to that report. then they able to see their pro id only.

that means here if a emp having only two pro id's then the global median will be shown for those two pro id's only.

But i want to get the value of global median irrespective of emp who logins to the report. means all the pro id revenue median that are there in my data.

 

i hope you understood my problem.

if you need any other info im happy to provide.

 

Please suggest me to solve this.

 

Thanks,

Mohan V

 

 

 

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: Median Comparison

@MohanV,

I am afraid that you can’t get the expected value of global median irrespective of employee who logins to the report.

Based on my test, in the case that we applied RLS to the report, even we create the global median measure by using ALL() function to remove filter from employee level, after publishing report to Service and share related dashboard to employees, employee will get a global median value based on how own data.

As a workaround, after calculating the global median in Power BI Desktop, use static value to represent global median in your measure.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 401 members 3,736 guests
Please welcome our newest community members: