How to do aggregation in column values instead of rows

[rohitthb]

Hi,

I am a bit novice in power BI and just starting up.

What I basically want to know is if summation/count etc can be put in first column rather than adding them in rows?

This is how it looks in excel. 

Can we create such looking matrix or table in power BI?

Thanks

Rohit

[MFelix]

Hi @rohitthb,

If you select the Matrix visuals you can add the rows/columns as you need.

Regrads,

MFelix

[rohitthb]

Hi @MFelix,

Thanks for your reply. I am not able to figure out the option which enables me to get aggregation in the column.

In excel this options come by dragging summation from column into rows in pivot.

I am clueless how to do this in power BI. If you could guide me it will be helpful.

Thanks

Rohit

[MFelix]

If you select the matrix you can add the colunns and the rows to the visual and in the values it does the summary so.if you put the accounts on columns and the values in the values you will get the same format, depending in the way you have the table set-up.

[dedelman_clng]

Hi @rohitthb - do you want the final table to look like the picture in your original post?  If so, where is the value 201601 coming from?  Is 201601 the name of a column in your table, or is that the value in a column ?  Some more information on where you are starting and exactly what you are trying to achieve would be helpful to troubleshoot.

[rohitthb]

hi @dedelman_clng,

Thanks for your reply. 

This is the sample data structure

City	yearmon	# of Bills	# of Customers	Revenue	# of Repeat Customers	Revenue from Repeat Customers
A	201601	2147	1735	525074	1054	400297
A	201602	2678	2114	784000	1326	564902
A	201603	2520	2009	503934	1169	210811
A	201604	2000	1844	680402	950	423141
A	201605	2000	1968	7829691	1142	299003
B	201601	2592	2079	1014188	1135	544405
B	201602	2605	2060	1068080	1020	779486
B	201603	2352	1898	927795	881	628698

I have City in filter and I want to show month on month sum of bills, customers, revenue etc calculated. I want 201601, 201602 to come in separate columns like in my earlier post.

Looking forward to your reply.

Thanks

Rohit

[MFelix · Accepted solution]

Hi @rohitthb,

Looking at your data I believe that the best way to do this is to unpivot the information you have and then do the matrix, this because if you add the columns you want to calculate to the matrix visual you will get the calculations but the name will not be there, again this is based on the information you made available.

I the Power Query view do as follow:

- Select all the columns to be calculated

- Unpivot columns

This will give you a new format with:

- City

- Value

- Yearmon

- Attribute - As the name of the columns # bills, # Customers,...

Then just add YearMon as columns, Value as value and attibute as Columns. You can then filter the attibute in the matrix visual if you don't want everything to show in the table or add a slicer. check the print screnns below.

Regards,

MFelix

[rohitthb]

Hi @MFelix,

First of all sorry for late response. I was out sick.

This is exactly what I was looking for and was not able to figure out. Super thanks for your answer.

Thanks

Rohit