Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am new to power BI and recently started making some dashboards reports.
I have a table as below.
Name | Score A | Score B | Score C | Score D |
College A | 7 | 4 | 4 | 3 |
College A | 3 | 3 | 6 | 4 |
College A | 5 | 5 | 8 | 5 |
College B | 2 | 6 | 8 | 6 |
College B | 6 | 3 | 2 | 2 |
College C | 2 | 5 | 4 | 8 |
College C | 4 | 3 | 2 | 4 |
College D | 8 | 4 | 6 | 3 |
College D | 6 | 7 | 7 | 4 |
College D | 2 | 4 | 8 | 2 |
Can any one help me summarize above table as
Name | Average Score A | Average Score B | Average Score C | Average Score D |
College A | 5 | 4 | 6 | 4 |
College B | 4 | 5 | 5 | 4 |
College C | 3 | 4 | 3 | 6 |
College D | 8 | 5 | 7 | 3 |
Instution | 5.00 | 4.50 | 5.25 | 4.25 |
in power bi and make a chart as follows.
I could do it in excel but not in power BI.
Solved! Go to Solution.
hi @slowlearner,
alternatively to the suggested DAX way you could use power query when getting the data into power bi so it fits your needs.
I made an pbix showing the transformations step by step - you would do it in a single step, so you do not end up with the intermediate steps.
https://www.dropbox.com/s/5fugnzrsvpwr9er/unpivot%20college%20chart.pbix?dl=0
steps were:
- 00 base data as you provided
- 01 aggregating data (00) to institution level (average for scores)
- 02 unpivot the score columns
- 03 unpivoting the base data (00)
- 04 union of (02) and (03)
best regards
florian
hi @slowlearner,
alternatively to the suggested DAX way you could use power query when getting the data into power bi so it fits your needs.
I made an pbix showing the transformations step by step - you would do it in a single step, so you do not end up with the intermediate steps.
https://www.dropbox.com/s/5fugnzrsvpwr9er/unpivot%20college%20chart.pbix?dl=0
steps were:
- 00 base data as you provided
- 01 aggregating data (00) to institution level (average for scores)
- 02 unpivot the score columns
- 03 unpivoting the base data (00)
- 04 union of (02) and (03)
best regards
florian
Hi @DaFloDo,
Thank you for your reply.
Do I have to manually do the step 02 and 03?
How do I automatically unpivot?
Thanks,
Santo
Hi @slowlearner,
you perform these steps only one time when you define your query. I would do this manually using the UI. Alternatively you can use the Power Query M code provided within the sample file as a starting point.
After that, each time you refresh the data (for example after the source has been updated) these steps are automatically performed to bring the data into your Power BI model.
best regards
Florian
Hi @slowlearner
To get the Score as Axis, you need to do this in Power Query and use Pivot as suggested by @DaFloDo .
Thanks
Raj
Hi @slowlearner
You can create a calculated table using the below DAX ( Go to Modelling tab -> New Table, use this code)
Avg summary = SUMMARIZE('Avg', ROLLUP('Avg'[Name]), "Avg of A", AVERAGE('Avg'[Score A]), "Avg of B", AVERAGE('Avg'[Score B]), "Avg of C", AVERAGE('Avg'[Score C]), "Avg of D", AVERAGE('Avg'[Score D]) )
2. Create a new column
Rename = IF(ISBLANK('Avg summary'[Name]),"Institution", 'Avg summary'[Name])
Use "stackd bar chart" visual to get your desired result.
Thanks
Raj
Thank you for the reply.
I need college names as an legend and average of A, B, C,D as an axis.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |