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 "...
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft 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