Questions re matrix data manipulation

[Helpful_Fun4848]

Hi all,

I have 2 questions regarding the matrix that I'm currently working on.

My data is a result of a survey and it looks like this:

Respondent	Division	Category	Survey Result
A	1	A	1
B	2	B	2
C	3	C	3

10 survey questions are 1 to 5 scale, 1 question 1 to 3 scale and 1 question is Yes (1) and No (0). I don't have problem calculating average score, Power BI can calculate it for each Category and/or Division perfectly, but when it comes to average %, then the calculation is not correct as Power BI calculate percentage of the grand total. 

First question: how do I create different calculation based on the scale on the same matrix? 

Second question: when I add Count of Respondent onto the matrix, it is showing up after each column. How do I make it so count of respondent only showing up on the last column only?

Division	Survey Result Category A	Count of Respondent	Survey Result Category B	Count of Respondent
1	1	1	1	1
2	2	2	2	2
3	3	3	3	3

Thanks all!

[Ashish_Mathur]

Hi,

Please share representative data and on that data show the expected result.

[v-shex-msft]

Hi @Helpful_Fun4848,

Did you mean to create a matrix as same as the sample you shared below? If that is the case, you can create a matrix with 'division', 'survey' fields as row, category field as the column, 'Respondent' field with summary mode 'count' on value fields.

#1, I'm not so clear about your requirement, can you please explain more about these?

How to Get Your Question Answered Quickly

#2, So you mean only showing the result on the corresponding category included in your table? For this scenario, you can write a measure formula and add the category filter in it to prevent the calculation of not-matched records.

Regards,

Xiaoxin Sheng

[Helpful_Fun4848]

@v-shex-msft 

Question 1: The matrix is like this:

Category	Average Score	Average %
A	Scale 1-5	Scale 1-5
B	Scale 1-5	Scale 1-5
C	Scale 1-5	Scale 1-5
D	Should be blank	1=Yes, 2=No
E	Should be blank	Already in pecentage
F	Scale 1-5	Scale 1-5
G	Scale 1-5	Scale 1-5
H	Scale 1-3	Scale 1-3

I created Average score which works for all, but, I need category D & E to be blank. How do I make it hidden to appear that it is blank?

I also created Average% measure, however, it won't work for all knowing Category D, E, & H requires different calculation. How do I, on the same matrix and column, add a measure just for Category D and H which is different than the rest?

If I create measure specifically for Category D, E, & H and adding it to the matrix, it will create additional 3 columns for that 3 measures. I want to keep the 2 columns result. 

Question 2: I want the matrix to look like this:

Division	Category A	Category B	Category C	Count of Respondent
1	1	1	1	1
2	2	2	2	2
3	3	3	3	3

However, currently, after I add Count of respondent, it show up after each columns like what I was showing on my original posting. 

Much thx!

[v-shex-msft]

Hi @Helpful_Fun4848,

#1, If you mean replace field value based on condition another field, you can directly do these in query editor with replacing value functions or write a measure expression with if statement to extract field value and ignore special value displayed.

Solved: Query editor replacing values based on another col... - Microsoft Power BI Community

measure =
VAR currCategory =
    SELECTEDVALUE ( Table[Category] )
VAR currScope =
    SELECTEDVALUE ( Table[Average Scope] )
RETURN
    IF ( currCategory IN { "D", "E" }, BLANK (), currScope )

#2, You can use the if statement or switch function with selectedvalue to check current row content and add expressions to replace calculations with these particular values.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

#3, You can drag the category fields to column fields and write measure expressions to switch expression results based on the current category.

Regards,

Xiaoxin Sheng

[Helpful_Fun4848 · Accepted solution]

@v-shex-msft 

I found the solution for question 1. I created 2 measures. Measure 1 for avg score, measure 2 for avg %. 

Measure 1 = 

if(or(max([Parent Category])="Parent Category1", max([Parent Category])="Parent Category2"),"N/A",AVERAGE([Value]))

Measure 2 = 

if(max([Parent category])="Parent Category1",AVERAGE([Value]),

if(max([Parent Category])="Parent Category2",AVERAGE([Value])/100,

(if(max([Parent Category])="Parent category3",AVERAGE([Value])/3,AVERAGE([Value])/5)))

)

I also found why count of respondent is showing up after each category. It is because on one category, there are 2 people who didn't completed the survey one 1 of the category. 

Much thx!