creating an aggregated table

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

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.