Forum Discussion

sapirmarko's avatar
sapirmarko
Icon for Helper I rankHelper I
3 years ago
Solved

break even point

Hi evryone,

I have ID and measure of Profit,

I want to crate a measure that count the number of ID that the sum of profit give me 0 (break even) 

 

for example the count(Id) =5 becouse after 5 the sum became -50 

IDProfitrank
customer 1301
customer 2202
customer 3103
customer 4-104
customer 5-405
customer 6-606
customer 7-707

 in long sory short I need to sum the profit coulmn until I get (-) by the order of rank coulmn

it's must to be in measre becouse it's dynamic 

thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI sapirmarko,

    I modify your formula and you can try to use the below measure formula if it helps:

    formula =
VAR curr = [Rank Profit]
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Wholesalers ),
        Wholesalers[whs_full_name],
        Wholesalers[Profit],
        "Rank", [Rank Profit]
    )
VAR addProfit =
    ADDCOLUMNS (
        summary,
        "S_Profit", SUMX ( FILTER ( summary, [Rank] <= curr ), [Profit] )
    )
RETURN
    COUNTX ( FILTER ( addProfit, [S_Profit] >= 0 ), [whs_full_name] )

    If the above also not help, can you please share some dummy data that keep the raw data structure and measure formulas to test? They will help us clarify your scenario to test to coding formula.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI sapirmarko,

    Did you mean to calculate the rolling profit based on rank order until the result exceed to the target? If that is the case, you can try to use the following measure formula:

    formula =
VAR currRank =
    MAX ( 'Table'[Rank] )
VAR target = -50
VAR rollResult =
    CALCULATE (
        SUM ( 'Table'[Profit] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Rank] <= currRank )
    )
RETURN
    IF ( rollResult >= target, rollResult )

    Regards,

    Xiaoxin Sheng

    • sapirmarko's avatar
      sapirmarko
      Icon for Helper I rankHelper I
      3 years ago

      this is not exactly what I need.

      I have this table - all the columns are measure (except of whs_full_name)

      measure 2 is thi:

      Measure 2 =
      var curr= [Rank Profit]
      var cum = SUMX(FILTER(ALL(Wholesalers[whs_full_name]),[Rank Profit]<=curr),[Profit])
      var tabl= SUMMARIZE(Wholesalers,Wholesalers[whs_full_name],"xx",cum)
      var z=  COUNTX(FILTER(tabl,[xx]>=0),Wholesalers[whs_full_name])
      return z
       
      I want to get the number 44 and I can't get to this result (meaning the last Rank Profit that measure 2=1)
       the table:

       

       
       
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        HI sapirmarko,

        I modify your formula and you can try to use the below measure formula if it helps:

        formula =
VAR curr = [Rank Profit]
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Wholesalers ),
        Wholesalers[whs_full_name],
        Wholesalers[Profit],
        "Rank", [Rank Profit]
    )
VAR addProfit =
    ADDCOLUMNS (
        summary,
        "S_Profit", SUMX ( FILTER ( summary, [Rank] <= curr ), [Profit] )
    )
RETURN
    COUNTX ( FILTER ( addProfit, [S_Profit] >= 0 ), [whs_full_name] )

        If the above also not help, can you please share some dummy data that keep the raw data structure and measure formulas to test? They will help us clarify your scenario to test to coding formula.

        How to Get Your Question Answered Quickly 

        Regards,

        Xiaoxin Sheng