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.

DominicBrien's avatar
DominicBrien
Frequent Visitor
8 years ago

Thanks, I did have it at the DAX level before I am now working on a new model where again I was trying to add the aggregation at the Report level.

Because I build the model using Excel I cannot add DAX column at the report level.

The measure works whenb displaying a list that has the one item per "Customer"...

The problem I get working with DAX measures is that the the end result also involves another level of aggregation that is not at the customer level.

I am working toward building a summarized visualisation that would translate in the context of the provided example to (values in the original sample wouls not result in this out but I hope you can understand where I am tryinng to go).

Number of customers

Total value range

3

Between 0 and 10

1

Between 10 and 20

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    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.

    • DominicBrien's avatar
      DominicBrien
      Frequent Visitor
      8 years ago

      Thanks, I changed my model quite a bit and was able to produce the result I wanted. Now I am struggeling with a new issue so It's probably better to start a new thread....