Forum Discussion
Continued ContributorCreate matric with data from same column
I have this dataset with multiple questions and answers:
The above shows only data from 2 question, but there are 18 different questions.
Is it possible in any way, using the data as they are represented above, to create a matrix that uses the question marked with blue on the row and the question marked with red on the column and then use the column 'Amount' as sum ?
I tried myself, but using the same column and filter for a question in the row rules out using the same question-column as column in a matrix. Am I missing a simple solution to this problem?
5 Replies
- JoeBarry
Solution Sage
Hi Bokazoit
To achieve this, you may need to duplicate the tables and filter the questions to Blue questions and Red Questions.
Create a Dimension table with the SurveyID as a distinct value. You should also have Date table.
Load the tables into Desktop and create a relationship with the Dimension table to both tables on the Survey ID column and the Date table on the Date column (Date table should be marked as date table) https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables
Create a measure
Amount = sum(BlueQuestions[Amount]) + sum(RedQuestions[Amount])In the Matrix, add the Blue Question in the Row section and the Red Question in the column section then add the Amount measure
BokazoitHmmm, I think I tried that, but will give it a try again. Return with the result if succes or failure 🙂
- Bokazoit
Can't be done, since I need to filter out the question:
It fails because I only need the answer for that particalur question on the row, and when I need answers for the column it says - forget it...
Am I doing something wrong?- JoeBarry
I have added a question dimension table also. It seems to have removed errors. I have created a PBIX to look through https://drive.google.com/file/d/1ECGfUUTD5iAgKQJckQb4f4VI34PdAoJ9/view?usp=sharing
