Stacked chart showing blank values.

[SM321]

Project	RACI	Dev RACI	Test RACI
A	Responsible	Responsible
B	Accountable		Accountable
C	Consulted
D	Informed	Accountable	Responsible
E	Responsible		Informed
F	Accountable	Consulted	Informed
G	Consulted
H	Informed	Informed
I
J
K
L

I am looking for a stacked chart, that would show RACI on the x-axis, with count of projects on Y, and also count of blanks for Dev and Test in the same stacked columns.

Like

Responsible would have a count of 2 (projects), 1 blank for Dev, and 1 blank for Test in the same column.

Consulted would have a count of 2 (projects), 1 blank for Dev, and 2 blank for Test in the same column.

Blank would have a count of 4 (projects), 4 blank for Dev, and 4 blank for Test in the same column.

I tried creating a separate table with SUMMARIZE, and COUNTBLANK – doesn’t help quite in my scenario.

[Anonymous · Accepted solution]

Hi @SM321 

Please try this:

I create three measures:

count RACI = 
	VAR _currentRACI = SELECTEDVALUE('Table'[RACI])
	RETURN
		CALCULATE(
			COUNTROWS('Table'),
			FILTER(
				ALLSELECTED('Table'),
				'Table'[RACI] = _currentRACI
			)
		)

Blank DEV = 
	VAR _CurrentRACI = SELECTEDVALUE('Table'[RACI])
	RETURN
		CALCULATE(
			COUNTROWS('Table'),
			FILTER(
				ALLSELECTED('Table'),
				'Table'[Dev RACI] = BLANK() && 'Table'[RACI] = _CurrentRACI
			)
		)

Blank Test = 
	VAR _currentRACI = SELECTEDVALUE('Table'[RACI])
	RETURN
		CALCULATE(
			COUNTROWS('Table'),
			FILTER(
				ALLSELECTED('Table'),
				'Table'[Test RACI] = BLANK() && 'Table'[RACI] = _currentRACI
			)
		)

The result is as follow:

Then create a new table with DAX: 

Table 2 = {"Project","DEV","Test"}

And create a measure:

CountProject = 
	VAR _ProjectValue = SELECTEDVALUE('Table 2'[Value])
	RETURN
		SWITCH(
			_ProjectValue,
			"Project", CALCULATE(
				DISTINCTCOUNT('Table'[RACI]),
				FILTER(
					ALLSELECTED('Table'),
					'Table'[RACI] <> BLANK()
				)
			),
			"DEV", CALCULATE(
				COUNTROWS('Table'),
				FILTER(
					ALLSELECTED('Table'),
					'Table'[Dev RACI] = BLANK()
				)
			),
			"Test", CALCULATE(
				COUNTROWS('Table'),
				FILTER(
					ALLSELECTED('Table'),
					'Table'[Test RACI] = BLANK()
				)
			)
		)

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.