Calculation for all but selected value using row level security

[Anonymous]

I am implementing row level security on a data model that includes aggregate tables. I am using the aggregate tables to report the totals for my organization. However, I also want to show a rate for all programs but the selected program that is being filtered via row level security. I want the calculation to be dynamically populated. Please see the table below for sample data.

yr_trm

stu_div

rolekey

cohort_p

retain_p

Cohort_Sum

Retain_Sum

Calculated Rate

Level

Calculate Rate for All but Selected GRANMS

1617FA

GR

GRPMGT

fall_2016_cohort

fall16_fall17_ret

320

220

69%

GR Program

1617FA

GR

GRISEM

fall_2016_cohort

fall16_fall17_ret

236

164

69%

GR Program

1617FA

GR

GRLTMS

fall_2016_cohort

fall16_fall17_ret

16

12

75%

GR Program

1617FA

GR

GRANMS

fall_2016_cohort

fall16_fall17_ret

172

134

78%

GR Program

69%

1617FA

GR

GRCSMS

fall_2016_cohort

fall16_fall17_ret

50

34

68%

GR Program

GR Total

794

564

71%

All GR Program

1617FA

UG

UGINSC

fall_2016_cohort

fall16_fall17_ret

92

62

67%

UG Program

1617FA

UG

UGCISC

fall_2016_cohort

fall16_fall17_ret

58

36

62%

UG Program

1617FA

UG

UGGSTC

fall_2016_cohort

fall16_fall17_ret

6

4

67%

UG Program

1617FA

UG

UGIMED

fall_2016_cohort

fall16_fall17_ret

24

20

83%

UG Program

1617FA

UG

UGMEBA

fall_2016_cohort

fall16_fall17_ret

28

16

57%

UG Program

1617FA

UG

UGBIOT

fall_2016_cohort

fall16_fall17_ret

30

26

87%

UG Program

1617FA

UG

UGANLY

fall_2016_cohort

fall16_fall17_ret

2

0

0%

UG Program

1617FA

UG

UGUNDC

fall_2016_cohort

fall16_fall17_ret

16

8

50%

UG Program

UG Total

256

172

67%

All UG Program

Grand Total

1050

736

Selected Program in Bold

[v-joesh-msft]

Hi @Anonymous ,

As this post says:

"The only solution to the problem is to create static measures or a separate table where you hold certain, company-wide metrics/aggregations/calculations. This separate table should not be tied to RLS and then you can use the aggregated metrics in that table in your calculations. "

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