DAX for Subgroup Percent Total works with PBI Filters

[Anonymous]

This is a long question. In general, I am looking for DAX that returns a single measure with subgroup percentages that works with filters on PowerBI.

I have data that looks like this:

Job#	Department	Race	Stage	Applicants
10	Sales	Black	Applied	12
10	Sales	Black	Interviewed	10
10	Sales	Black	Selected	0
10	Sales	Other	Applied	14
10	Sales	Other	Interviewed	2
10	Sales	Other	Selected	1
10	Sales	White	Applied	20
10	Sales	White	Interviewed	15
10	Sales	White	Selected	2
11	Marketing	Black	Applied	9
11	Marketing	Black	Interviewed	4
11	Marketing	Black	Selected	1
11	Marketing	Other	Applied	5
11	Marketing	Other	Interviewed	0
11	Marketing	Other	Selected	0
11	Marketing	White	Applied	16
11	Marketing	White	Interviewed	12
11	Marketing	White	Selected	11
12	HR	Black	Applied	19
12	HR	Black	Interviewed	17
12	HR	Black	Selected	12
12	HR	Other	Applied	15
12	HR	Other	Interviewed	12
12	HR	Other	Selected	0
12	HR	White	Applied	0
12	HR	White	Interviewed	0
12	HR	White	Selected	0
13	Sales	Black	Applied	1
13	Sales	Black	Interviewed	0
13	Sales	Black	Selected	0
13	Sales	Other	Applied	14
13	Sales	Other	Interviewed	5
13	Sales	Other	Selected	4
13	Sales	White	Applied	18
13	Sales	White	Interviewed	9
13	Sales	White	Selected	7

I am trying to make a bar graph that looks like this:

Here is the DAX that I used to create this bar graph:

MeasureA = DIVIDE (

SUM ( 'table'[Applicants]),

(CALCULATE (

SUM ( 'table'[Applicants] ),

ALLEXCEPT('table', 'table'[Stage])

)))

Here is alternative DAX that also works:

MeasureB = (CALCULATE(SUM('table'[Applicants]),FILTER('table','table'[Race]=MAX('table'[Race])&&'table'[Stage]=MAX('table'[Stage]))))/CALCULATE(SUM('table'[Applicants]),FILTER(ALL('table'),'table'[Stage]=MAX('table'[Stage])))

The issue is: I have a filter on the side for department and when I filter it to only Sales (or any department for that matter), the percentage totals for each race do not equal 100 and I want them to. Instead it is the percentage of the grand total which also includes the other departments:

Is there a way to modify this DAX so it will work with the departments filter? So it doesn’t still calculate percentage as a whole.

[daxreport]

Hello, Are you able to share me this file (with some sample data)? I am working on a report and might have a similiar problem. Thanks! 

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

MeasureA = DIVIDE (

SUM ( 'table'[Applicants]),

(CALCULATE (
SUM ( 'table'[Applicants] ),
filter(allselected('table'), 'table'[Stage] =max('table'[Stage]))
)))

Best is that you create a new table for stage , and then you can use all or allselected
MeasureA = DIVIDE (

SUM ( 'table'[Applicants]),

(CALCULATE (
SUM ( 'table'[Applicants] ),
filter(allselected('Stage'), 'Stage'[Stage] =max('Stage'[Stage]))
)))