Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Measure that sum with filter after aggregation

Hello everyone,

Here is my problem:

I need to create a measure that sum all the value greater than 0.05 after aggregation.

I have a table like this:

Name	Value
A	0.05
A	0.01
A	0.02
B	0.01
B	0.01
B	0.02
C	0.01
C	0.04
C	0.02

The aggregation looks like this but I don't want to create a new table for this:

Name	Value
A	0.08
B	0.04
C	0.07

The measure should return 0.15 because only A and C are greater than 0.05.

Can this be done in DAX without creating a new aggregated table?

Thank you for the incoming answers,

David

Mariusz
6 years ago
Hi Anonymous

This should do the trick

Measure = SUMX( VALUES( 'Table'[Name] ), CALCULATE( VAR __sum = SUM( 'Table'[Value] ) RETURN IF( __sum > 0.05, __sum ) ) )

Best Regards,
Mariusz

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

Anonymous oops, try this

Measure 2 = 
 
SUMX ( 
VALUES ( Test[Name] ), 
VAR __s = CALCULATE( SUM ( Test[Value] ) ) 
RETURN IF ( __s > 0.05, __s ) 
)

4 Replies

Mariusz
Community Champion
6 years ago
Hi Anonymous

This should do the trick

Measure = SUMX( VALUES( 'Table'[Name] ), CALCULATE( VAR __sum = SUM( 'Table'[Value] ) RETURN IF( __sum > 0.05, __sum ) ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
parry2k
Super User
6 years ago
Anonymous use this measure

Sum over value = VAR _s = SUM ( Test[Value] ) RETURN SUMX ( FILTER( VALUES ( Test[Name] ), _s > 0.05 ), _s )
- Anonymous
  Not applicable
  6 years ago
  Hi parry2k,
  
  Thanks for your time.
  
  The result is not what I expect. The measure gives 0.57 (which is 3 times the total value)
  - parry2k
    Super User
    6 years ago
    Anonymous oops, try this
    
    Measure 2 = SUMX ( VALUES ( Test[Name] ), VAR __s = CALCULATE( SUM ( Test[Value] ) ) RETURN IF ( __s > 0.05, __s ) )