Percentage measure with multiple criteria that automatically changes with slicer selections

[onedayover]

Hi

I have 3 slicers on my page - region, mps_flag, level to filter a "Top 3 sites" bar chart and "Total adoption" card visual.

I've tried to create a measure to calculate percentages, "total_user_adoption_%", however I need some help in getting it to work correctly. I'm trying to have the measure automatically omit rows that have a zero user_count and/or rows that have a site_flag of "Corp" from the calculation.

For example...

1. When no filters are selected, Site O3 shows in the "Top 3 sites" chart as 48.55%. Working correctly, this should show a percentage of 97.01%. The total visual also shows 28.31% and should show 40.18% with nothing selected in the slicers.

site	site_name	region	type	site_flag	mps_flag	level	user_count	level_user_count
O3	Site O3	SER	Student	School	Primary	1	199	193
O3	Site O3	SER	Student	School	Primary	2	190	204
O3	Site O3	SER	Student	School	Primary	3	162	171
O3	Site O3	SER	Student	School	Primary	4	0	75
O3	Site O3	SER	Staff	Corp	Primary		0	201
O3	Site O3	SER	Student	School	Primary	5	0	149
O3	Site O3	SER	Student	School	Primary	6	0	142

2. Similarly, if I select, for example, level 4 & 9 from the level slicer, it shows Site O5, Site O4, Site O2 and again the percentages aren't what I need them to be.

Top 3 sites...
Site O5 shows as 11.09%, however it should show as 90.09%
Site O4 shows as 8.07%, however it should show as 46.54%
Site O2 shows as 2.04%, however it should show as 39.75%

The "Total adoption" visual shows as 5.27%, however it should show as 58.63%.

site	site_name	region	type	site_flag	mps_flag	level	user_count	level_user_count
O1	Site O1	DSR	Student	School	Primary	04	15	125
O2	Site O2	MER	Student	School	Secondary	04	41	47
O2	Site O2	MER	Student	School	Secondary	09	23	114
O4	Site O4	FNR	Student	School	Secondary	09	74	159
O5	Site O5	SER	Student	School	Secondary	04	1	18
O5	Site O5	SER	Student	School	Secondary	09	308	325

This is my attempt at doing this...

total_user_adoption_% =
MIN(1,DIVIDE( CALCULATE(SUMx('users',if('users'[site_flag] <> "Corp" && 'users'[user_count] > 0,'users'[user_count],0))),
CALCULATE(SUM('users'[level_user_count]),
ALL ( 'users'[region] ),
ALL ( 'users'[mps_flag] ),
ALL ( 'users'[type]),
ALL ( 'users'[level] ) ))
)

I'd appreciate any help with getting this measure to work correctly no matter what combination is selected in any of the slicers.

Many thanks

[onedayover]

@lbendlin hi, just wondering if what I replied was what you were asking? Is what I'm asking possible? 

Thanks

onedayover

[lbendlin]

Ah, so these are independent.  Grades in school type.  

What is the business problem you are trying to solve?

[onedayover]

Hi lbendlin,

Thanks again for getting back to me.

Wow reading over my original post and I've just realised how confusing it is, my apologies, it was clear in my mind at the time lol. Let me try to explain better.

I have a table where sites listed have up to 12 levels. Each of these levels has a count of the users who are engaged in a program (user_count). The table also has a total user count for that site/level (level_user_count). The "mps_flag: column defines whether the site is a School or Corp site. Corp sites are not to be included in the percentage calculations.

I have 3 slicers on my page - region, mps_flag, level which filter a "Top 3 sites" bar chart.

So, what I am trying to calculate is the "Top 3 sites" percentage no matter what is, or what's not, filtered in the slicers. The calculation is based on the fields "user_count" divided by "level_user_count".

This is an example table which I hope will help.

Please let me know if anything isn't clear here.

Thanks
onedayover

site	site_name	region	type	site_flag	mps_flag	level	user_count	level_user_count
O1	Site O1	DSR	Student	School	Primary	1	0	92
O1	Site O1	DSR	Student	School	Primary	2	12	118
O1	Site O1	DSR	Student	School	Primary	3	26	120
O1	Site O1	DSR	Student	School	Primary	4	15	125
O1	Site O1	DSR	Staff	Corp	Primary		16	142
O1	Site O1	DSR	Student	School	Primary	5	0	92
O1	Site O1	DSR	Student	School	Primary	6	0	101
O2	Site O2	MER	Student	School	Secondary	1	13	14
O2	Site O2	MER	Student	School	Secondary	2	14	15
O2	Site O2	MER	Student	School	Secondary	3	26	26
O2	Site O2	MER	Student	School	Secondary	4	41	47
O2	Site O2	MER	Student	School	Secondary	5	54	1033
O2	Site O2	MER	Student	School	Secondary	6	55	269
O2	Site O2	MER	Student	School	Secondary	7	0	267
O2	Site O2	MER	Student	School	Secondary	8	0	254
O2	Site O2	MER	Student	School	Secondary	9	23	114
O2	Site O2	MER	Staff	Corp	Secondary		16	272
O2	Site O2	MER	Student	School	Secondary	10	0	242
O2	Site O2	MER	Student	School	Secondary	11	14	314
O2	Site O2	MER	Student	School	Secondary	12	0	269
O3	Site O3	SER	Student	School	Primary	1	199	193
O3	Site O3	SER	Student	School	Primary	2	190	204
O3	Site O3	SER	Student	School	Primary	3	162	171
O3	Site O3	SER	Student	School	Primary	4	0	75
O3	Site O3	SER	Staff	Corp	Primary		0	201
O3	Site O3	SER	Student	School	Primary	5	0	149
O3	Site O3	SER	Student	School	Primary	6	0	142
O4	Site O4	FNR	Student	School	Secondary	7	54	162
O4	Site O4	FNR	Student	School	Secondary	8	24	160
O4	Site O4	FNR	Student	School	Secondary	9	74	159
O4	Site O4	FNR	Student	School	Secondary	10	62	195
O4	Site O4	FNR	Student	School	Secondary	11	37	129
O4	Site O4	FNR	Student	School	Secondary	12	0	112
O5	Site O5	SER	Student	School	Secondary	1	0	0
O5	Site O5	SER	Student	School	Secondary	2	0	17
O5	Site O5	SER	Student	School	Secondary	3	0	17
O5	Site O5	SER	Student	School	Secondary	4	1	18
O5	Site O5	SER	Student	School	Secondary	5	18	19
O5	Site O5	SER	Staff	Corp	Secondary		0	19
O5	Site O5	SER	Student	School	Secondary	6	178	181
O5	Site O5	SER	Student	School	Secondary	7	1	193
O5	Site O5	SER	Student	School	Secondary	8	186	177
O5	Site O5	SER	Student	School	Secondary	9	308	325
O5	Site O5	SER	Student	School	Secondary	10	224	470
O5	Site O5	SER	Student	School	Secondary	11	467	525
O5	Site O5	SER	Student	School	Secondary	12	3	699

[lbendlin]

Something like this?

[onedayover]

Hi again, I just tried this and no sorry, it didn't calculate the percentages I need. I did remove the ALLEXCEPT('Table','Table'[site]) part and I got near the result I was expecting. The screenshots below might explain it better...

If I was to filter by region and select "FNR" and "SER" (excluding Corp sites), the percentages I would expect to see in the "Top 3 sites" and the "Total adoption" card are...

This is the result I'm actually getting...

With nothing selected in any slicer, I would expect the percentages to look like...

And thanks, I appreciate your patience with this.

onedayover

[lbendlin · Accepted solution]

You say "Top 3 sites" but then you introduce other conditions.  This means that for each of your visuals you will to manually have to specify which filter choices to apply and which to ignore. You can use REMOVEFILTERS and KEEPFILTERS for that, or you can clean up your visuals to only contain the site column (but not type and site_flag, for example)

[onedayover]

Yes, I need the "top 3 sites" to change depending on what is selected in the filters. I will take another look at this and see if I can work it out based on your advice. 

Thanks

onedayover

[lbendlin]

Can you consider the values for Level 1 and Level 2  together or is Level 2 to be treated as a subset/dependency of Level 1, in a hierarchy?

[onedayover]

Hi lbendlin, I'm not entirely sure what you mean. All levels are independent of each other, however they could be considered as a hierarchy in the following way...

Lower Primary
1
2

Middle Primary
3
4

Upper Primary
5
6

Lower Secondary
7
8

Middle Secondary
9
10

Upper Secondary
11
12