Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

creating an aggregated table

Hi all, I have a table that I want to create an aggregate table based off of. A customer can have multiple customer IDs. I want the desired table to contain all the distinct IDs - no repeats. And revenue in the new table should be the sum of the revenue from the ids (for Harvard, 150+200). Anyone know how to do this?

 

Current table

Customer ID      Customer     Revenue    Region     
1Stanford U100US-West
2Stanford U50US-West
3Harvard U150US-East
3Harvard U200US-East

 

Desired Table

Customer ID  Customer       Total Revenue        Region      
1Stanford U100US-West
2Stanford U50US-West
3Harvard U350US-East
3 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

At least two ways to do this.  

 

1.  Recommended Way - do it in Power Query.  Group By multiple columns (ID, Customer, and Region) and do a sum on the Revenue column.  This will make it so you only have to load one table into your data model.

 

2. Do it with a DAX table.  With this approach, you'll end up with two tables in your data model.  You can ignore the primary table in your analysis, but it will take up filespace.  Click on New Table on the Modelling tab and enter this expression

 

SummaryTable = ADDCOLUMNS(SUMMARIZE(Table, Table[ID], Table[Customer], Table[Region]), "Revenue", Calculate(Sum(Table[Revenue]))) //replace Table with your actual table name

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

MarcoPessina
Resolver IV
Resolver IV

Hi @Anonymous,

you can try this:

Table2 = SUMMARIZECOLUMNS (
    Table1[Customer ID],Table1[Customer],Table1[Region],"Total Revenue", SUMX ( Table1,Table1[Revenue]))

It could be more efficient than using SUMMARIZE according to sqlbi:
https://www.sqlbi.com/articles/introducing-summarizecolumns/ 

 

Hope it helps.

Cheers,

Marco

View solution in original post

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You may create calculated table like DAX below.

 

Desired Table = SUMMARIZE(Table1,Table1[Customer ID],Table1[Customer],Table1[Region],"Total Revenue", SUM(Table1[Revenue]))

107.PNG

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You may create calculated table like DAX below.

 

Desired Table = SUMMARIZE(Table1,Table1[Customer ID],Table1[Customer],Table1[Region],"Total Revenue", SUM(Table1[Revenue]))

107.PNG

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MarcoPessina
Resolver IV
Resolver IV

Hi @Anonymous,

you can try this:

Table2 = SUMMARIZECOLUMNS (
    Table1[Customer ID],Table1[Customer],Table1[Region],"Total Revenue", SUMX ( Table1,Table1[Revenue]))

It could be more efficient than using SUMMARIZE according to sqlbi:
https://www.sqlbi.com/articles/introducing-summarizecolumns/ 

 

Hope it helps.

Cheers,

Marco

mahoneypat
Employee
Employee

At least two ways to do this.  

 

1.  Recommended Way - do it in Power Query.  Group By multiple columns (ID, Customer, and Region) and do a sum on the Revenue column.  This will make it so you only have to load one table into your data model.

 

2. Do it with a DAX table.  With this approach, you'll end up with two tables in your data model.  You can ignore the primary table in your analysis, but it will take up filespace.  Click on New Table on the Modelling tab and enter this expression

 

SummaryTable = ADDCOLUMNS(SUMMARIZE(Table, Table[ID], Table[Customer], Table[Region]), "Revenue", Calculate(Sum(Table[Revenue]))) //replace Table with your actual table name

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.