cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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?

 

Capture.PNG

 

 

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Highlight rows with similar amounts within a certain % difference

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Frequent Visitor

Re: Highlight rows with similar amounts within a certain % difference

Correct. 

Highlighted
Frequent Visitor

Re: Highlight rows with similar amounts within a certain % difference

Bump

Highlighted
Super User IV
Super User IV

Re: Highlight rows with similar amounts within a certain % difference

Hi @esegovia

 

Would this not solve your issue with the measures below.

 

Personal Portion = CALCULATE(SUM('Table2'[Current]),'Table2'[PayerName] = "Personal Portion")

Private Pay = CALCULATE(SUM('Table2'[Current]),'Table2'[PayerName] = "Private Pay")

% Difference = DIVIDE([Personal Portion],[Private Pay])

And then if you put this into a table with the ID I got the following output?

 

A-PBI Community.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Microsoft
Microsoft

Re: Highlight rows with similar amounts within a certain % difference

 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

Re: Highlight rows with similar amounts within a certain % difference

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors