cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mohit23 Frequent Visitor
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

Accepted Solutions
Super User
Super User

Re: Using pivot data as raw data for calculation

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.

2 REPLIES 2
Super User
Super User

Re: Using pivot data as raw data for calculation

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.

Super User
Super User

Re: Using pivot data as raw data for calculation

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 340 members 3,300 guests
Please welcome our newest community members: