Sum of a row passing two conditions

[srpeters]

Is there a way to create a measure that returns the sum of a column for each row grouped by two conditions without using the group by feature in Power Query? Here is some sample data to explain:

The measure would have to return the total cost for each customer split up by each Product ID. For example, if I used this measure in a calculated column, the measure would return 6 not 3 for the second to last row. I can not group the columns in Power Query because I need the months. I tried to use calculate and filter by Customer ID >= Earlier(Customer ID) and Product ID >= Earlier (Product ID) after sorting both columns in the Power Query but this did not seem to work due to the size of the table I am working with. 

Thank you!

[Greg_Deckler · Accepted solution]

@srpeters Try this (below) You can use the measure in a table visual with Customer ID and Product ID

Column = 
  VAR __Customer = [Customer ID]
  VAR __Product = [Product ID]
  VAR __Table = FILTER( 'Table', [Custmer ID] = __Customer && [Product ID] = __Product )
  VAR __Result = SUMX( __Table, [Cost] )
RETURN
  __Result


Measure = 
  VAR __Customer = MAX( [Customer ID] )
  VAR __Product = MAX( [Product ID] )
  VAR __Table = FILTER( 'Table', [Custmer ID] = __Customer && [Product ID] = __Product )
  VAR __Result = SUMX( __Table, [Cost] )
RETURN
  __Result

[Rupak_bi · Accepted solution]

Hi @srpeters 

Here is the solution

Sum = calculate(sum('Table (6)'[Cost]),ALLEXCEPT('Table (6)','Table (6)'[Coustomer ID],'Table (6)'[Product ID]))

you can use the same DAX in Measure as well

[srpeters]

This worked great, thank you!

[Greg_Deckler · Accepted solution]

@srpeters Try this (below) You can use the measure in a table visual with Customer ID and Product ID

Column = 
  VAR __Customer = [Customer ID]
  VAR __Product = [Product ID]
  VAR __Table = FILTER( 'Table', [Custmer ID] = __Customer && [Product ID] = __Product )
  VAR __Result = SUMX( __Table, [Cost] )
RETURN
  __Result


Measure = 
  VAR __Customer = MAX( [Customer ID] )
  VAR __Product = MAX( [Product ID] )
  VAR __Table = FILTER( 'Table', [Custmer ID] = __Customer && [Product ID] = __Product )
  VAR __Result = SUMX( __Table, [Cost] )
RETURN
  __Result