cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 1 Stanford U 100 US-West 2 Stanford U 50 US-West 3 Harvard U 150 US-East 3 Harvard U 200 US-East

Desired Table

 Customer ID Customer Total Revenue Region 1 Stanford U 100 US-West 2 Stanford U 50 US-West 3 Harvard U 350 US-East
3 ACCEPTED SOLUTIONS
Microsoft 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!

@mahoneypa HoosierBI on YouTube

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

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]))``

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.

3 REPLIES 3
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]))``

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.

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

Microsoft 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!

@mahoneypa HoosierBI on YouTube

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors