locked
Aggregate columns do not aggregate as expected. RRS feed

  • Question

  • My problem is that I cannot find a way to create columns aggregated by some other column. I feel there must be a way, because it´s basics, but perhaps I just cannot find it. 

    Let´s say, we have a table with 3 columns [OrderID, CustomerID, Sales]. 

    I want to calculate sum of sales per customer (or some more complex aggregate measure, but let's just try the sum, because it's easier to check). 

    I can do it 2 ways - first, add CustomerID and Sales to the table (or diagram, but table is easier to check) and select "Sum" from dropdown list. 

    That works, Sales are summarised by Customer

    But then, I want to do the same with DAX (because potentially, I will be creating more complicated expressions). So I create an aggregate column, that's supposed to be aggregated "according to context", because I am not using ALL filter: 

    SumSales = SUM([Sales])

    I add the SumSales column to my table, and select "Do not summarise" from dropdown list, because the column is already summarised. 

    By my thinking, it should be the same as the second column of this same table (sum of [Sales] aggregated by customer ID). 

    But it's not. It calculates SUM([Sales]) OVER THE ENTIRE TABLE ALWAYS, so the third column is not the same as the second one (sum of sales per customer), but it's the same value all the time. 

    Please help!!! How can I aggregate with DAX!!!!



    Monday, July 13, 2015 4:56 PM

Answers

  • You don't want a custom column, you want a measure or "Calculated Field", it's syntax is:

    SumSales:=SUM([Sales])

    And then you put that in a table or matrix and it will do what you want, filter itself based upon the values in the row it is in. Go to PowerPivot and on the home page choose "Calculation Area" and then put it anywhere in the calculation area.

    Monday, July 13, 2015 5:56 PM