Forum Discussion
Sum With Multiple Filters
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
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
Anonymous
How to get the unique values from another table and comparing multiple column in both table.
Please advise.
Regards.,
Swamy