DAX Sum or Max value greater than input value(slicer)

[Anonymous]

Hi,

I have table columns sublock,elementpath, value(integer). sublock and elementpath forms unique key.

i have input slicer to enter scalar value integer value like 10, 44.input slicer sublock has values 1a,1b,1c to 1s, 2a,2b,2c to 2s, 4a,4b,4c to 4s

i created running total measure (RT_M)for input column value.

if user selects sublock slicer 4F. then report visual starts from 4F.i created distance measure from input slicer it will generate distance values(ex: 4f 0, 4g 1,4h 2) so i can sort visual by distance.If user enters 50 as input,i need to get max running total or sum up to the block. example in below screen shot for input 50 , running total greater than or equal to input value(50) reaches at block 4H.

I need to get sum value up to block 4H(from 4F to 4H) that is 54.47. I created measure that is working correct 80 percent of time , somet times its giving incorrect value. please help. thanks for ur time.

Below is the measure.

EndingMW_M =

var CurrDistance = [TargetMW_Dis]

var result = Calculate(SUM('EEP vwMWValue'[Value]),Filter(ALL('EEP vwMWValue'),'EEP vwMWValue'[D_I_M] >=0 &&'EEP vwMWValue'[D_I_M]<= CurrDistance))

RETURN

result

in above  [TargetMW_Dis] is measure max distance from block 4F to 4 H that is 2.

D_I_M is measure to calculate distance from slicer selection block(4F)

 

 

 

[Greg_Deckler]

@Anonymous Responding to your PM. You could try this version of your measure below. This would at least allow you to troubleshoot using TOCSV(__Table) in your return so that you could see what is and is not being returned by the FILTER statement. Otherwise, difficult to know what exactly the problem is without sample data, measure formulas and such.

EndingMW_M =
var CurrDistance = [TargetMW_Dis]
var __Table = Filter(ALL('EEP vwMWValue'),'EEP vwMWValue'[D_I_M] >=0 &&'EEP vwMWValue'[D_I_M]<= CurrDistance)
var result = SUMX( __Table, [Value])
RETURN
result

[wdx223_Daniel]

EndingMW_M =

var CurrDistance = [TargetMW_Dis]

var result = Calculate(SUM('EEP vwMWValue'[Value]),'EEP vwMWValue'[D_I_M] >=0 &&'EEP vwMWValue'[D_I_M]<= CurrDistance)

RETURN

result

[Anonymous]

throwing below error.thanks for ur time

[Anonymous]

i tried below also. its giving incorrect result.

EndingMW_M =

var CurrDistance = [TargetMW_Dis]

var result = Calculate(SUM('EEP vwMWValue'[Value]),Filter('EEP vwMWValue','EEP vwMWValue'[D_I_M] >=0 &&'EEP vwMWValue'[D_I_M]<= CurrDistance))

RETURN

result