Forum Discussion

dtem's avatar
dtem
Regular Visitor
3 years ago
Solved

Conditional formatting when values are similar

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.

 

CompanyAccount1Account2Account3Account4Account5Account6
Company1  0  -78701725.99  -678225154.9  -490016689.6  0-1296516724  
Company2  84650482.76  00001843534.90
Company3  678225154.9  00-47471943.940-13427928.55
Company4  490016689.6  047471943.940-1217982574  -325274417
Company5  0  00121798257400
Company6  1296516724  -1701843.6213427928.55325274417.100

 

Thank you all for your assistance!

Conditional Formatting
DAX
Table Visual
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.