Replacing columns with rows

[aktripathi2506]

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.

[arify]

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 🙂

[aktripathi2506]

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.

[v-sihou-msft · Accepted solution]

@aktripathi2506

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:

1. Add an Index column(from 1).
   
2. Right click on Index column, select "Unpivot other columns".
   
3. Click on "Attribute" column, select "Group By".
   
4. Set "Group by" like below. You will get expect result.
   
   

Regards,

[arify]

Sorry, I don't have much knowledge about the Service, we'll need someone else's help on that.

Thanks

[arify]

(You may want to ask the new question in the Service subforum)

[Habib]

Not possible in Power BI currently. There is idea already logged for this. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10948962-matrix-measures-or-value...

[Vvelarde]

The more closest is use a Multicard Row.