Forum Discussion

murphm6's avatar
murphm6
Helper II
3 years ago
Solved

Compare values across multiple rows with same ID to create flags

Hi everyone,

 

I'm running into a problem where I need to compare multiple rows in a table to see if a certain charge type is higher than another. 

 

Here is what the data looks like: Essentially, I want to look at each 'Short Invoice' and create a flag (simple 1,0) if there is a 'Cost' that is higher than the 'Ocean Freight' cost. How can I compare across multiple rows where they all have a unique ID?

 

Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    Try the adding the following column to your table. (Replace costTable with your table name.)

     

    Grtr Ocean =
    var _freightValue =
    // find Ocean Freight value for given short invoice
    CALCULATE(
        MAX(costTable[Cost]),
        ALLEXCEPT(costTable,costTable[Short Invoice]),
        costTable[Charge Type] = "Ocean Freight"
    )
    var _result =
    // test if cost is greater than Ocean Freight value for given short invoice
    IF(
        [Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight",
        1,
        0
    )
    Return
    _result

4 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    Try the adding the following column to your table. (Replace costTable with your table name.)

     

    Grtr Ocean =
    var _freightValue =
    // find Ocean Freight value for given short invoice
    CALCULATE(
        MAX(costTable[Cost]),
        ALLEXCEPT(costTable,costTable[Short Invoice]),
        costTable[Charge Type] = "Ocean Freight"
    )
    var _result =
    // test if cost is greater than Ocean Freight value for given short invoice
    IF(
        [Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight",
        1,
        0
    )
    Return
    _result
    • murphm6's avatar
      murphm6
      Helper II
      3 years ago

      This seems to be on the right track. However I just ran it and when filtering for values that have '1', some of the returned 'Short invoices' don't have any ocean freight costs. Is there a way to exclude short invoices that don't have any associated 'OCean freight' charge types?

      • jgeddes's avatar
        jgeddes
        Super User
        3 years ago

        Amend the IF test line to

        [Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight" && NOT(ISBLANK(_freightValue))