Forum Discussion

ABC11's avatar
ABC11
Icon for Resolver I rankResolver I
2 years ago
Solved

want to see within GOALIMIT ROW

Hello to All,

Only want to see one row per LEMNUM - within GOALIMIT when LEMNUM is same- color in BLUE

As example

LEMNUM 322185 - LINE_COST 279,000>250,000 and 279,000<=500,000 so PERSONID- MATTHEGE do not have enough GOALIMIT so approver would be GLENNS so I would like to show only blue row 

LEMNUM 322197- MATTHEGE has enough GOALIMIT to approve.

 

PERSONGROUPNAMECNRCMSLEMNUMWAPPR_DATEPERSONID GOALIMIT  LINE_COST NO_OF_DAYS
LEMHZHMHEAVY R0155893221851/8/2024GLENNS              500,000.00       279,000.000
LEMHZHMHEAVY R0155893221851/8/2024MATTHEGE              250,000.00       279,000.000
LEMHZHMHEAVY R0155893221971/8/2024GLENNS              500,000.00       373,200.000
LEMHZHMHEAVY R0155893221971/8/2024MATTHEGE              250,000.00       233,000.000
LMBP02GRAHAM R0331123204271/1/2024DANSO              250,000.00       119,612.457
LMBP02GRAHAM R0331123204271/1/2024ROBBERTR              500,000.00       119,612.457
LMBP08ABCR0180073220291/8/2024ANDREWC              100,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024BLAKETA              250,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024CHRISFAU              250,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024DANSO              250,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024DANTEC                50,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024GREGMA              250,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024JEFFWH          2,500,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024JOHNAL          2,000,000.00       259,950.400
LMBP08ABCR0180073220291/8/2024ROBBERTR              500,000.00       259,950.400
LMBP09XYZR0327123157031/2/2024DANSO              250,000.00         16,756.406
LMBP09XYZR0327123157031/2/2024JASONTA          2,500,000.00         16,756.406
LMBP09XYZR0327123157031/2/2024ROBBERTR              500,000.00         16,756.406
LMBP09XYZR0327123157041/2/2024DANSO              250,000.00         17,686.506
LMBP09XYZR0327123157041/2/2024JASONTA          2,500,000.00         17,686.506
LMBP09XYZR0327123157041/2/2024ROBBERTR              500,000.00         17,686.506
LMBP09XYZR0327123158841/8/2024DANSO              250,000.00         11,257.840
LMBP09XYZR0327123158841/8/2024JASONTA          2,500,000.00         11,257.840
LMBP14DGFR0191513196961/8/2024CHADBE          2,500,000.00           3,218.870
LMBP14DGFR0191513196961/8/2024CHRISFAU              250,000.00           3,218.870
LMBP14DGFR0191513196961/8/2024DANTEC                50,000.00           3,218.870
LMBP14DGFR0191513196961/8/2024NATM              250,000.00           3,218.870

 

Thanks for your time and help

ABC11

 

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    2 years ago

    ABC11 
    There are multiples ways. You can create a new table that filters only the final approvers.
    under modling tab, create a new table as follows:

    New Table = FILTER( Table03 , Table03[PERSONID] = Table03[APPROVER] )

    Result:

     




  • Fowmy's avatar
    Fowmy
    2 years ago

    ABC11 

    Here are the modified codes:

    Calculated Column:

    APPROVER = 
VAR __LemnumFiltered =  FILTER( Table03 , Table03[LEMNUM] = EARLIER(Table03[LEMNUM]) && Table03[ GOALIMIT ] > Table03[ LINE_COST ] )
VAR __ApproverAmount = MINX( __LemnumFiltered , Table03[ GOALIMIT ] )
VAR __Result = CONCATENATEX(  FILTER( __LemnumFiltered , Table03[ GOALIMIT ] = __ApproverAmount ) , Table03[PERSONID],"|" )
RETURN
    __Result


    New Table:

    New Table = FILTER( Table03 ,  CONTAINSSTRING( Table03[APPROVER], Table03[PERSONID] ) )



18 Replies

    • ABC11's avatar
      ABC11
      Icon for Resolver I rankResolver I
      2 years ago

      Hello GREG,

      Thanks for your quick respond.

      LEMNUM 322185- MATTHEGE can't approve this becuase his GOALIMIT upto 250,000 and my LINE_COST value is 279,000. so in this case GLENNS is approver.

      LEMMUN 322197- MATTHEGE has enough GOALIMIT to approve. so do not want to forward this to GLENNS(GLENNS has higer GOALIMIT compare to MATTHEGE)

      I hope - this will help us

      Thanks again

       

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

    ABC11 

    Please explain
    1. Why the LINE COST numbers are different for 322197 ?
    2. For number 322029 ROBBERTR qualifies first to approve but you highlighted JEFFWH




    • ABC11's avatar
      ABC11
      Icon for Resolver I rankResolver I
      2 years ago

      Hello Fowmy,

      LEMNUM#322197-it was my mistake(when I was dummy preparing data) it should be 233,000

      LEMNUM#322029-you are correct ROBBERTR should be the approver.

      Sorry for this mistake

      Thanks for looking into-would you please help me here

       

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

        ABC11 

        Add the following calculated column to your table:

        APPROVER = 
VAR __LemnumFiltered =  FILTER( Table03 , Table03[LEMNUM] = EARLIER(Table03[LEMNUM]) && Table03[ GOALIMIT ] > Table03[ LINE_COST ] )
VAR __ApproverAmount = MINX( __LemnumFiltered , Table03[ GOALIMIT ] )
VAR __Result = MAXX(  FILTER( __LemnumFiltered , Table03[ GOALIMIT ] = __ApproverAmount ) , Table03[PERSONID] )
RETURN
    __Result

         

         

  • ABC11's avatar
    ABC11
    Icon for Resolver I rankResolver I
    1 year ago

    Hello,

    I would like to have my line cost total accurate. May I please request to some one can help me here or I need to open new thread

    Thanks

    ABC11