Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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)

 

 

 

 

 

DAX SUM

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago
    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's avatar
      Anonymous
      Not applicable
      3 years ago

      throwing below error.thanks for ur time

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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