Calculating median of entire group to compare against 1 record result in group

[ConorMairs]

Hey,

I need some help in Power BI Desktop for my new report. I work at head office for a company where we own over 400 sites and I am wanting to build a "Site Dashboard" which shows key metrics by individual site

I have a measure which calculates our lost customers based on the "Children" table and I am looking to establish a median lost customer for the full cohort to compare against the selected centre. I want to showcase this on a graph with lost customers by age group (0-5) and by tenure (0-6 months, 6-12 months, 1-2 years, 2-3 years, 3 years +). 

Our reporting structure uses row level security, so a site manager who manages 1 site will not be able to see any results from other sites, so this has proved tricky in calculating the 'other sites cohort'.

Any help on how's best to get around this would be helpful. See attached info on my key relationships and below table where I have manually calculated the median for the full centres cohort and what I am wanting to achieve in my BI report.

Children Age Group Last Booking	Median Lost Customers
0	15
1	15
2	15
3	15
4	15
5	16

[Thejeswar]

Hi @ConorMairs ,

You can try having a separate Summarized Calculated tables based on Age Group and by Tenure with the median of Lost Customers. These tables should not be connected to the data model by any relationships. This will avoid the Row Level Security from impacting these table.

Try using these tables to display a comparison between the Site Selected and for all the sites value.

One downside that I can think of is, you cannot have the data for both the site selected and for all sites in a single visual. You need to use separate visuals to show these data

Regards,