Aggregating values from different tables to another column in a Parent Table

[lemmallari]

I currently have multiple tables, let's call them PortalData1, PortalData2, PortalData3 and PortalData4. These four tables have the following columns, Hits, Ratings, Date. Mock Data is shown below (let's assume for now that all values in all 4 tables are the same):

Hits	Ratings	Date
100	3	Date 1
200	4	Date 2
300	5	Date 3

I created a new table called Portal with columns PortalID and PortalName. I then added columns to the first four tables I created called PortalID to link them with the Portal table I created. So for exmaple, PortalData1 now looks like this:

Hits	Ratings	Date	PortalID
100	3	Date 1	1
200	4	Date 2	1
300	5	Date 3	1

Portal table looks like this:

PortalID	PortalName
1	Portal1
2	Portal2
3	Portal3
4	Portal4

What I need now is to have a Hits column in my Portal table so that it will show the sum of all hits per Portal. So for example, Portal1 has a total of 100, Portal2 has a total of 200 and son on, I would like my Portal table to look like this:

PortalID	PortalName	Hits
1	Portal1	100
2	Portal2	200
3	Portal3	300
4	Portal4	400

Is this possbile via PowerBI? How should I approach this? Do I need to create a measure or a calculated column? I'm pretty new to Power BI so any help or suggestion will be appreciated. Thanks!

[MattAllington]

---

@lemmallari wrote:
 

Is this possbile via PowerBI? How should I approach this? Do I need to create a measure or a calculated column? I'm pretty new to Power BI so any help or suggestion will be appreciated. Thanks!

---

Yes it is possible. The easiest is to use power query (get data) to append the 4 data tabkes into 1. Alternatively you can link all 4 tables (data tables) to the one lookup table.  Read about multiple data tables here http://exceleratorbi.com.au/multiple-data-tables-power-pivot/

[lemmallari]

Hi! Thanks for your answer, unfortunately I'm still new to Power BI and have no idea how to do the things you mentioned. Would you have a step by step procedure to that? The link you provided from my understanding, assumes that the reader is already familiar with Power BI.

[v-caliao-msft · Accepted solution]

@lemmallari

Yes, it is possible, you could refer to below code to get total hits.

 Total = SUMX(FILTER(SELECTCOLUMNS(UNION(PortalData1,PortalData2,PortalData3,PortalData4),"ID",[PortalID],"Name",[PortalName],"Hit",[Hits]),[ID]=Portal[PortalID]),[Hit])

Reference function:
UNION : Use to merge records.
SELECTCOLUMNS: Rename columns.
SUMX: get the total amount.

Regards,

Charlie Liao