Sum all values with greater than it's value

[syntax1995]

Hi Team,,

I am new with Dax. 

I would like to ask if is it possible to create a measure that will calculate the sum of all values except those values that are less than its value.

Here is the example.

ID	Total Spend	Desired Result
A	250	250
B	200	450
C	100	550

- A(250) is greater than B and C so the result should be 250 only.

- B(200) is greater than C(100) but less that A(250) so you only add A(250) + B(200) = 450

- C(100) is less than A(250) and B(200) so the calculation would be A+B+C = 550

Also, the measure should recalculate when being filtered.

Thank you so much for your help in advance.

[Ashish_Mathur]

Hi,

This measure works

Measure = SUMX(FILTER(ALL(Data[ID]),[TS]>=MIN(Data[Total Spend])),[TS])

Hope this helps.

[Anonymous]

Hi @syntax1995 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Desired Result =
CALCULATE (
    SUM ( 'Table'[Total Spend] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Total Spend] >= MAX ( 'Table'[Total Spend] ) )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[syntax1995]

Hi @Anonymous ,

This great. This works well, however, I need the result to be recalculated when being filtered, lets say I try to filter out A(250) then the sumation should not include the value of A(250) since it is being filtered.

Thank you so much in advance.

[Anonymous · Accepted solution]

Hi @syntax1995 ,

Modify the measure value to:

Desired Result =
CALCULATE (
    SUM ( 'Table'[Total Spend] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Total Spend] >= MAX ( 'Table'[Total Spend] )
    )
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[syntax1995]

Hi @Anonymous ,

This works ! Great ! Thank you so much !