Forum Discussion
Questions re matrix data manipulation
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!
Anonymous
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!
5 Replies
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
Anonymous
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!
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.
#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
Hi,
Please share representative data and on that data show the expected result.
