Forum Discussion

esegovia's avatar
esegovia
Frequent Visitor
9 years ago

Highlight rows with similar amounts within a certain % difference

I am trying to create an accounts receivable report that highlights rows with similar "Current" amounts for each ID number to indicate an audit check.  

 

The payers will be prefiltered to either "Private Pay" or "Personal Portion".  "Current" is a calculated measure based on aging buckets. 

 

For the ID number with highlight amounts in the screenshot below, I've highlighted the amounts that should be within 1% difference to be considered flagged. Assume one amount is a credit in parentheses and the other is a debit or a positive number.   Does anyone have any DAX code suggestions that could get me started?

 

 

 

6 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago
    Hi there, so you are looking for a DAX measure which will effectively divide the ($970.00) by the $982.00 for the ID Number 5208?
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

     Hi esegovia,

     

    Maybe you could try this measure. 

    1. [Current] is the measure that already exists;

    2. "COUNT ( 'table'[PayerName] ) = 2" means there are two payers;

    3. The formula of percentage can be adjusted.

     

    flag =
IF (
    COUNT ( 'table'[PayerName] ) = 2,
    IF (
        ABS (
            CALCULATE ( [Current], 'table'[PayerName] = "Personal Portion" )
                - CALCULATE ( [Current], 'table'[PayerName] = "Private Pay" )
        )
            / CALCULATE ( [Current], 'table'[PayerName] = "Personal Portion" )
            <= 0.1,
        1,
        0
    ),
    0
)

    Best Regards!

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi esegovia,

     

    Could you please mark the proper answer if it's convenient for you? That will be a help to others.

     

    Best Regards!
    Dale