Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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.
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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |