Forum Discussion
Replacing columns with rows
Hi,
In my table suppose I have 5 column A, B, C, D and E. I have values for them.
Example:
| A | B | C | D | E |
| 5 | 22 | 15 | 32 | 4 |
| 10 | 4 | 8 | 25 | 9 |
| 11 | 9 | 6 | 4 | 4 |
| 21 | 4 | 8 | 12 | 11 |
| 25 | 11 | 2 | 10 | 4 |
| 32 | 4 | 8 | 7 | 15 |
| 25 | 15 | 9 | 22 | 8 |
| 4 | 8 | 7 | 4 | 6 |
| 12 | 6 | 6 | 9 | 8 |
Now when I display the average of these column it comes like this:
| Avg of A | Avg of B | Avg of C | Avg of D | Avg of E |
| 16 | 9 | 7 | 14 | 7 |
But I want to display it like below:
| Avg of A | 16 |
| Avg of B | 9 |
| Avg of C | 7 |
| Avg of D | 14 |
| Avg of E | 7 |
Please advise.
Thanks a lot.
In Power BI Desktop, you can directly use your large input table and return the average of each item without exporting the data at all. Please refer to steps below:
- Add an Index column(from 1).
- Right click on Index column, select "Unpivot other columns".
- Click on "Attribute" column, select "Group By".
- Set "Group by" like below. You will get expect result.
Regards,
- Add an Index column(from 1).
7 Replies
Not possible in Power BI currently. There is idea already logged for this.
The more closest is use a Multicard Row.
Hi,
Once you have the 2nd table:
Avg of A Avg of B Avg of C Avg of D Avg of E 16 9 7 14 7 You can select all columns (click first column, then shift+click last column), right click, pick "Unpivot Columns". That will produce exactly what you want :)
Hi,
I have a big input table, which has several columns including columns A, B, C,D and E.
2nd table I am getting is as a result. Do you know any way to use this table as an input other than exporting the data and uploading it again to power bi and then doing unpivot the columns.
Because I want less mannual efforts.
Thanks.
Sorry, I don't have much knowledge about the Service, we'll need someone else's help on that.
Thanks
