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.

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

Hi DominicBrien,

 

You can achieve this with simple DAX expressions:

 

Calculated Column:

 

SUM Value Column =
CALCULATE (
    SUM ( SampleTable[Value] ),
    FILTER (
        SampleTable,
        SampleTable[CustomerID] = EARLIER ( SampleTable[CustomerID] )
    )
)

Measure:

 

SUM Value Measure =
CALCULATE (
    SUM ( SampleTable[Value] ),
    ALLEXCEPT ( SampleTable, SampleTable[CustomerID] )
)

 

Thanks,
Xi Jin.