Count records until it hitting target Amount

mbpowerbi 

here is a workaround for you

Measure = sum('Table'[Amount])

Measure 2 = if([_sum]<10500,1,0)

Measure 3 = sumx(VALUES('Table'[Sub]),[Measure 2])

 

 

mbpowerbi well here is another option on top of what ryan_mayu has shared:

Sub Count = 
VAR __Max = 10500    
VAR __Table = 
    WINDOW ( 
        1, ABS, 
        0, REL, 
        ALLSELECTED ( 'Table'[Amount] ), 
        ORDERBY ( 'Table'[Amount] ) 
    ) 

VAR __TableWithRT = 
    ADDCOLUMNS ( 
        __Table,  
        "@Sum", 
        VAR __Amt = [Amount] 
        RETURN 
            SUMX ( FILTER ( __Table, [Amount] <= __Amt ), [Amount] ) 
    ) 
RETURN 
COUNTROWS ( 
    FILTER ( __TableWithRT, [@Sum] <= __Max )
)

Hi mbpowerbi ,

Thanks to parry2k  and ryan_mayu  for the quick reply and solution. I have other ideas to share as well:

1.Create a numeric range parameter.

2.Create a column.

Rank = RANKX('Table',[Dollar Amount],,ASC,Dense) 

3.Create measures.

SUM_Measure = 
CALCULATE(SUM('Table'[Dollar Amount]),FILTER(ALLSELECTED('Table'),[Rank]<=MAX('Table'[Rank])))

Measure = 
var _slicer=SELECTEDVALUE('Parameter'[Parameter])
var _talbe=ADDCOLUMNS('Table',"sum",[SUM_Measure])
RETURN MaxX(FILTER(_talbe,'Table'[Rank] in VALUES('Table'[Rank]) && [sum]<=_slicer),[Rank])

4.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.