Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Sum With Multiple Filters

here's an easier one... or at least it should be, I've looked in previous posts but didn't see this situation addressed... I am attempting to write a formula to sum column c in the below example, under the following conditions:
1. column a contains the same value
2. column b is a distinct value

3. I need to sum column c under the condition that the data from column a is similar (same part number), but only if the location is unique.

4. for the purposes of my data model I need this to occur as a column rather than a measure.

It seems like it should be simple to resolve, but so far all of my idea have returned the incorrect value.

Need Help

Anonymous

7 years ago

Hi Anonymous ,

I do agree with you that it isn't elegant. Here is a solution that works :

TOTAL = CALCULATE(SUMX('Table';'Table'[QTY AVAILABLE]/
COUNTX ( FILTER ( 'Table'; EARLIER ( 'Table'[LOCATION] ) = 'Table'[LOCATION] ); 'Table'[LOCATION] ) );ALLEXCEPT('Table';'Table'[PN]))

If you want to understand how it works, I described it below

1. Find the number of occurences of each LOCATION

OCCURENCES = COUNTX ( FILTER ( 'Table'; EARLIER ( 'Table'[LOCATION] ) = 'Table'[LOCATION] ); 'Table'[LOCATION] )

2. Divide QTY AVAILABLE by the number of occurences found

CalculatedQTY = 'Table'[QTY AVAILABLE]/'Table'[OCCURENCES]

3. Sum this calculated occurences

RESULT = CALCULATE( SUM( 'Table'[CalculatedQTY] ); ALLEXCEPT( 'Table'; 'Table'[PN] ))

Tell me if it is any better for you,

Regards,

Etienne

12 Replies

v-frfei-msft

Community Support

7 years ago

Hi Anonymous ,

Please check the following steps as below.

1. Insert an index column in power query.

2. Create the calculated columns as below.

Column = var ind = 'Table'[Index] -1
return
IF(CALCULATE(MAX('Table'[PN]),FILTER('Table','Table'[Index] = ind)) <>'Table'[PN],1,0)

catgory = CALCULATE(SUM('Table'[Column]),FILTER('Table','Table'[Index] <= EARLIER('Table'[Index])))

Result = CALCULATE(SUM('Table'[QTY AVAILABLE]),ALLEXCEPT('Table','Table'[catgory]))

Mariusz
Community Champion
7 years ago
Hi Anonymous

Please see the below
Column = CALCULATE( SUM( 'Table'[QTY AVAILEBLE] ), ALLEXCEPT( 'Table', 'Table'[PN] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Anonymous
Not applicable
7 years ago
Mariusz, Thank you but this suggestion does not meet the criteria I specified. It simply sums Qty available... The challenge is that if the QTY Available from the same location appears on multiple rows, the total will be duplicated.
- Mariusz
  Community Champion
  7 years ago
  Hi Anonymous
  
  Sorry I was under ipression that this was the requierment.
  
  Best Regards,
  Mariusz
  
  If this post helps, then please consider Accepting it as the solution.
  
  Please feel free to connect with me.
  - Anonymous
    Not applicable
    7 years ago
    Hi Mariusz , I think that Anonymous wants to ignore duplicates in his table.
    You will probably have a better solution but one way to do it is to first remove duplicates from your table
    Table2 = DISTINCT('Table')
    And then use Mariusz formula
    Column = CALCULATE( SUM( 'Table'[QTY AVAILEBLE] ), ALLEXCEPT( 'Table', 'Table'[PN] ) )
    It's not very clean and i think you could have a much better result by using Power Query instead of dax, but at least this will work.