Forum Discussion

DominicBrien's avatar
DominicBrien
Frequent Visitor
8 years ago
Solved

Aggregate in same table

Hello, I am trying to do aggregation in the same table... Here is what I am looking for.... Basically I want the SUM(Value) Group by CustomerID. The capture above came from my work around an...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi DominicBrien,

     

    => I am working toward building a summarized visualisation that would translate in the context of the provided example to.

     

    Did you mean that you want to create a new calculated table with SUMMARIZED() function based on the original table?

     

    Maybe something like:

     

    Table =
SUMMARIZE (
    'TableName',
    "Between 0 and 10", CALCULATE (
        COUNT ( 'TableName'[CustomerID] ),
        FILTER ( 'TableName', 'TableName'[Value] <= 10 )
    ),
    "Between 10 and 20", CALCULATE (
        COUNT ( 'TableName'[CustomerID] ),
        FILTER ( 'TableName', 'TableName'[Value] > 10 && 'TableName'[Value] <= 20 )
    )
)

    Thanks,
    Xi Jin.

MarkS's avatar
MarkS
Resolver IV
8 years ago

Hi DominicBrien,

Use the Group By function and the advanced functions,

Group By - CustomerID

Aggregations - Sum of Value Column

                      -  ALL Rows

 

Then expand the Table

 

Here if the M code appended to the end of your code 

let
Source = Table.FromRecords({[CustomerID = 1, Name = "Bob", Value = 10],[CustomerID = 1, Name = "Bob", Value = 11],[CustomerID = 3, Name = "Paul", Value = 25],[CustomerID = 4, Name = "Ringo", Value = 42]}),
#"AddingIdx" = Table.AddIndexColumn(Source,"Index"),
#"AddingTot" = Table.AddColumn(#"AddingIdx", "Total", each Table.Range(#"AddingIdx",0,[Index]+1)),
#"Aggr" = Table.AggregateTableColumn(#"AddingTot", "Total", {{"Value", List.Sum, "cSales"}}),
#"Select" = Table.AddColumn(#"Aggr", "SubTotal", each Table.SelectRows(#"Aggr", each ([CustomerID]=1))),
#"Aggr2" = Table.AggregateTableColumn(#"Select", "SubTotal", {{"Value", List.Sum, "cSales2"}}),
    #"Grouped Rows" = Table.Group(Aggr2, {"CustomerID"}, {{"GroupTotal", each List.Sum([Value]), type number}, {"GroupedTable", each _, type table}}),
    #"Expanded GroupedTable" = Table.ExpandTableColumn(#"Grouped Rows", "GroupedTable", {"Name", "Value", "Index", "cSales", "cSales2"}, {"Name", "Value", "Index", "cSales", "cSales2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded GroupedTable",{"CustomerID", "Name", "Value", "Index", "cSales", "cSales2", "GroupTotal"})
in
#"Reordered Columns"