dax for % answering YES in column

[adavid999]

Apols for this simple question - i have been away from Dax for a few weeks and can't figure out, but I would like to convert table below:

	Q1
North	Yes
South	No
North	Null
West	Yes
South	No

to show the percent of Yes for each region. Like a crosstab in spss but just % answering yes for each question and aggregated by region. It would ideally look something like:

	Q1 % answering Yes
North	50
South	30
East	75
West	30

Is there an easy way to do this? I managed myself using 3 different measures to come up with % Yes (e.g. count yes, count no, sum yes+no, % yes is no yes/total), but I have around 50 questions (so wld require 150 measures) and was wondering if there is a quicker way. I.e. is there one measure I can do per column to capture % yes?

Many thanks,

A

[Anonymous]

// Table structure:
// Region | Question | Answer
// A	  | Q1		 | Yes
// B	  | Q1       | No
// A      | Q2       | (Blank)
// ...

// Having the above, you can create
// your measure that will calculate
// the % of "Yes" in any selection
// of regions and questions.

[% Yes] =
var __yesCount =
	CALCULATE(
		COUNTROWS( T ),
		KEEPFILTERS( T[Answer] = "Yes" )
	)
var __totalCount = COUNTROWS( T )
var __result =
	DIVIDE( 
		__yesCount,
		__totalCount
	)
return
	__result

The above measure takes into account all the answers, even (blank)'s. If you want to ignore (blank)'s, then this measure will do it:

// Table structure:
// Region | Question | Answer
// A	  | Q1		 | Yes
// B	  | Q1       | No
// A      | Q2       | (Blank)
// ...

// Having the above, you can create
// your measure that will calculate
// the % of "Yes" in any selection
// of regions and questions.

[% Yes] =
var __yesCount =
	CALCULATE(
		COUNTROWS( T ),
		KEEPFILTERS( T[Answer] = "Yes" )
	)
var __totalCount = 
	CALCULATE(
		COUNTROWS( T ),
		KEEPFILTERS(
			NOT ISBLANK( T[Answer] )
		)
	)
var __result =
	DIVIDE( 
		__yesCount,
		__totalCount
	)
return
	__result

Best

D

[AlB · Accepted solution]

Hi @adavid999 

I understand the responses are in a different column for each question, correct? If so, you should unpivot the columns in the query editor, so that you have one column with the answer, one with the region and one with the question number. Once you've done than, you can do what you need with just one measure.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[adavid999]

Thank you @AlB I will try this. I am working in excel power pivot and it seems there is less functionality compared to power query but I will hunt around

[AlB]

@adavid999 

You have Power Query in Excel too. And it certaily has the functionality to pivot/unpivot columns

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers