Median Comparison

[Anonymous]

Hi,

I have some revenue datas for several projects :

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

[TomMartens]

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

[Anonymous]

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

[v-yuezhe-msft]

@Anonymous,

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