Forum Discussion
Questions on Data Format for Analysis
You can consider using a "normalized" format with the following approach:
Suggested Data Structure
1. Entity Column: Add a column to identify the source of feedback, i.e., whether it’s from a "Student" or "Teacher".
2. Feedback Index Column: Use another column to indicate the feedback instance (e.g., "Feedback 1" or "Feedback 2").
3. Feedback Value Column: Place the feedback values in a single column rather than having separate columns for Feedback 1 and Feedback 2.Example Structure
Subject Entity Feedback Index Feedback Value Math Student Feedback 1 4 Math Student Feedback 2 3 Math Teacher Feedback 1 5 Math Teacher Feedback 2 N/A With this format:
- You can easily filter by "Student" or "Teacher" in Power BI.
- To calculate the total feedback count for each entity, you’d only need to count non-null entries in the "Feedback Value" column, grouped by the "Entity" column.This normalized structure makes it easier to analyze each entity’s feedback separately and helps create flexible Power BI visuals based on user requirements.
Hi alvin1999 ,
Store each feedback record separately, rather than combining multiple feedbacks into the same row. This allows for easier statistics and analysis. The following is a suggested format:
Analyzing Ideas:
- Count the number of feedbacks: Use COUNT or COUNTROWS function to count the number of records whose Feedback Type is Student and Teacher respectively.
- Feedback Distribution Analysis: Create bar charts or pie charts to show the distribution of feedback from students and teachers respectively.
- Time Trend Analysis: Create line charts to show the trend of feedback changes in different time periods.
Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To analyze your dataset effectively in Power BI, a few formatting adjustments and storyline ideas can enhance the visualization:
Data Formatting Suggestions:
Restructure Feedback Fields: Since each student and teacher can give up to two feedback entries, arrange the feedback fields into separate columns for each person’s response (e.g., Student_Feedback_1, Student_Feedback_2, Teacher_Feedback_1, Teacher_Feedback_2). This way, Power BI can calculate and compare responses easily.
Create a Unified Feedback Column:
- Use Power Query to unpivot the feedback columns, creating one unified feedback column with corresponding categories (Student or Teacher) to make it easier to analyze trends across different groups.
Handle NA Values: Replace NA values with a blank or "No Response" to distinguish between feedback given and not given. This will help you analyze participation rates.
Analysis and Storyline Ideas:
Feedback Participation Rates:
- Display the count of feedbacks given vs. not given for students and teachers, using a bar chart to show participation rates.
Feedback Comparison:
- Analyze and compare the types of feedback provided by students and teachers, perhaps using a clustered bar chart to see patterns in responses.
Sentiment Analysis (if feedback has a text component):
- If feedback includes qualitative text, consider a word cloud or sentiment analysis visual to identify recurring themes or sentiments in feedback.
Overall Feedback Trends:
- A stacked column chart can illustrate the distribution of feedback types or sentiment by subject, showing areas with higher or lower engagement or satisfaction.
Please leave a Kudos or Mark as Solution if it helps.
Hi saurav_0101 ,
Thank your for your suggestion.
For data formating suggestion number 1 -Restructure Feedback Field, by having first feedback of each party to the feedback 1 column and second feedback to feedback 2 column, similar to student and teacher may cause that I am unable to sum the number of feedback provided by student and teacher separately.
Do you have better way to get the total feedback for student and teacher separately?