Forum Discussion

SM321's avatar
SM321
Frequent Visitor
2 years ago
Solved

Stacked chart showing blank values.

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.