Forum Discussion

mbpowerbi's avatar
mbpowerbi
New Member
2 years ago
Solved

Count records until it hitting target Amount

Hello - could you please help me create a Dax formula to help me with this problem.

I'm trying to count how many subs it will take to reach our Total Amount target and I would like to be able to count the subs from the small dollar amount to the large dollar amount. 

In this example the total amount to reach is $10,500, so by adding up the dollar amount from small to large, it took 4 subs to reach the target total amount.

 

 

Thank you,

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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])
     
     

     

  • parry2k's avatar
    parry2k
    2 years ago

    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 )
)
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

     

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    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])
     
     

     

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        sry , that one was missing. pls see the coding below

        _sub =
        VAR _current=[Measure]
        return sumx(FILTER(all('Table'),[Measure]<=_current),[Measure])
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    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 )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    The first 4 numbers add up to 10,404.  Since your threshold is 10,500, shouldn't the answer be 5?