Reconciling 2 Data Sets

[Koli]

Hello,

I hope all is well. I am trying to reconcile 2 different data sets. One of the problems I am running to is rounding and conditional formatting.
For example:

Sum of Amount          2nd Amount

$130.00                             $129.99998

Essentially these are the same numbers with rounding

I would like the conditional formatting to read .. if sum of amount is almost the sum of the 2nd amt then place remark, else null. 

I know that's not the correct operator. However this is what I'm trying to get to. Is this possible?

Thank you!

[lbendlin]

if(round(a,0)=round(b,0) && a<>b,"flag")

[Koli]

Thank you for the information. Would this be placed in conditional formatting or hardcoded in the editor function line of Power Query?

[lbendlin]

This would be a DAX measure.

[Koli]

Hello,

Thank you for the information. I looked up DAX usage in Power Query , however, when I went to try to add the query to a data model, I received error message memory. I have 32 bit excel. Here is a picture of one of the errors. I am loaded as connection only

 

[lbendlin]

you are missing the "and" part of the rule, the Power Query equivalent for the DAX "&&".

[Koli]

Hello,

Thank you again for your assistance. I am still receiving and error message. I really don't know what I am doing wrong.🤔

[lbendlin]

Power Query mandates the use of the "else"  branch.

[Koli]

Hello,

Thank you again for the quick response! Unfortunately, this is not working. I am sure this is user error. When I put in the formula it doesn't bring back the desired results as seen in the initial example. 

Could you please help in writing the formula out using the fields on the previous screenshot in power query, so that I can see what I am doing wrong? I am a newbie to power query. Thank you in advance!

For example:

Sum of Amount          2nd Amount

$130.00                             $129.99998

Essentially these are the same numbers with rounding

I would like the conditional formatting to read .. if sum of amount is almost the sum of the 2nd amt then place remark, else null. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Koli]

• There not an exact match, but almost an exact match

Sum of Fee.1	Sum of Fee.2	AF Variance	Rounding	Remarks should say
135.41	135.412	-0.002	0.00	Rounding
430.59	430.5862	0.0038	0.00	Rounding
135.41	135.412	-0.002	0.00	Rounding
430.59	430.5862	0.0038	0.00	Rounding
135.41	135.41	0	0.00	Rounding
430.59	430.5862	0.0038	0.00	Rounding

[lbendlin]

all of your sample data is "almost" a match, and it seems to be based on two decimals.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Koli · Accepted solution]

Hello,

Thank you for responding. I was able to figure it out. I used conditional formatting,

if AF variance is less than or equal to zero then rounding.

Thank you!