cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Using pivot data as raw data for calculation

Basically, I am trying to create something similar to Pivot table in excel and then do calculations using fields in that Pivot table. 

Currently I have data which looks like this: 

ID MonthColumn 1Column2
5426May10
5432May10
5466May10
5499May10
5426April01
5432April01
5466April01
5499April01

 

If I were using Excel, I would create a pivot table on this data and create a view like following :

Desired Data 
ID Column 1Column2
542611
543211
546611
549911

 

I just don't know how to achieve this result in Power BI. Once I have this desired result then I want to do more calculations using aggregated values in Column1 and Column2. 
Point is I want to do calculations on "aggregated" values of Column1 and Column2 and not the raw data. 


Any suggestions will be appreciated. 

 

Thanks.

 

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi,

To your Table visual, drag ID to the row labels.  Write these measures

Column1 total = SUM(Data[Column 1])

Column2 total = SUM(Data[Column 2])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2

While you “can” load data this way, this is not the best approach. A better approach (generally speaking) is to unpivot the data. Have the column names as attributes and the values in a new column. I have a video showing how here https://youtu.be/K7VS5oqjCxU

 

once you have this structure, you can drag the columns into a visual in Power BI and build the visuals you need using the aggregates. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User III
Super User III

Hi,

To your Table visual, drag ID to the row labels.  Write these measures

Column1 total = SUM(Data[Column 1])

Column2 total = SUM(Data[Column 2])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors