Conditional formatting when values are similar

[dtem]

Hi All,

I have a Table Visual that shows my data as shown below where I have business units in one company that conduct business with each other. Account1 belongs to Company1, Account2 belongs to Company2, Account3 belongs to Company3 etc. If all the transactions that occur over a period have been captured in both companies correctly, the sum of Account1 in Company2 and Account2 in Company1 should be equal to zero. I would like to conditionally format the corresponding accounts in each company such that the ones that give a sum of zero turn green and the ones that give a sum that is not equal to zero turn red. What is the best way to go about this? Kindly note the column with company names is also a value in the table.

Company	Account1	Account2	Account3	Account4	Account5	Account6
Company1	0	-78701725.99	-678225154.9	-490016689.6	0	-1296516724
Company2	84650482.76	0	0	0	0	1843534.90
Company3	678225154.9	0	0	-47471943.94	0	-13427928.55
Company4	490016689.6	0	47471943.94	0	-1217982574	-325274417
Company5	0	0	0	1217982574	0	0
Company6	1296516724	-1701843.62	13427928.55	325274417.1	0	0

Thank you all for your assistance!

[v-jianboli-msft · Accepted solution]

Hi @dtem ,

Please try:

First, unpivot the Account columns:

Then create a matrix visual:

Conditional format:

Measure = 
var _a = "Account"&RIGHT(SELECTEDVALUE('Table (2)'[Company]),LEN(SUBSTITUTE(SELECTEDVALUE('Table (2)'[Company]),"Company","")))
var _b = "Company"&RIGHT(SELECTEDVALUE('Table (2)'[Attribute]),LEN(SUBSTITUTE(SELECTEDVALUE('Table (2)'[Attribute]),"Account","")))
var _c = CALCULATE(SUM('Table (2)'[Value]),FILTER(ALL('Table (2)'),[Attribute]=_a&&[Company]=_b))
return IF(SUM('Table (2)'[Value])+_c=0,"Green","Red")

Final output:

Best Regards,

Jianbo Li

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