I have a format expression like below. SUM(A...

expressions
(John Henry Joseph) #1

I have a format expression like below.

SUM(ACCOUNTHEADS[Debit]) <>

SUM(ACCOUNTHEADS[Credit])

If Both figure Sum of “Debit” and “Credit” not tallied, the particular field value become red. As per the records, both figures are tallied, see the test expression picture below. But system shows “False”… what is the problem here? please help me out.

(Reza Raoofi) #2

The test results seem to be correct, because it returns false to not-equal operator (<>) which means both SUM values are evaluated as equal.

(John Henry Joseph) #3

@RezaRaoofi I want the format expression in such way that if both “Debit” and “Credit” are not tallied each other… so what is the correct expression please?

(John Henry Joseph) #4

Thanks lot Mr. Reza. It works in different way…actually i forgot the basic rule - system will check the condition row by row. I created two virtual columns to store “sum of debit across the table” and “sum of credit across the table”. Now i put expression similar to yours. Thanks again.

(Reza Raoofi) #5

You are welcome! I am glad you worked it out.

(Reza Raoofi) #6

I don’t know what your column structure is, but I am guessing perhaps the SUM values you have calculated always are tallied, meaning you are not calculating any non-equal SUMs of Debit/Credit to trigger the format rule.

(John Henry Joseph) #7

@RezaRaoofi Table name is “ACCOUNTHEADS” Columns are “Debit” and “Credit” both are “price” type.

(Reza Raoofi) #8

Yes, but if you sum Credit/Debit across all rows in that table, you will always have only one value for each SUM, unless you have some sort of grouping by another column in order to have different sum values and the format rule evaluate that condition against different SUM values.

(Bellave Jayaram) #9

You probably need something like: SUM(SELECT(ACCOUNTHEADS[Debit], [Customer]=[_THISROW].[Customer])) <> SUM(SELECT(ACCOUNTHEADS[Credit], [Customer]=[_THISROW].[Customer]))

(John Henry Joseph) #10

@Bellave_Jayaram I believe that customer-wise check not required as i try to check both “Debit” and “Credit” figures are matched across the table “AccountHeads”. By above my original expression, it was working fine…but now it turned out… i dont know why?

(Reza Raoofi) #11

Well, we know the expression in your Format rule’s condition, also the first screenshot was showing the right evaluation result (False) from that expression; can you send a screenshot of the AccountHeads table’s column structure?

(John Henry Joseph) #12

@RezaRaoofi Attached below is the AccountHeads Table column structure please.

(Reza Raoofi) #13

Hmm, I think you need to remove those SUM() functions from your format rule condition; perhaps something like: AccountHeads[Debit] <> AccountHeads[Credit] or AccountHeads[Debit Total] <> AccountHeads[Credit Total] or AccountHeads[Debit Balance] <> AccountHeads[Credit Balance]

Each row in AcocuntHeads must have different distinct values for [Debit…]/[Credit…] columns in order for format rule to work; otherwise if you SUM across all rows in AccountHeads table like SUM(AccountHeads[Debit]) in format rule’s condition, it will always return the same SUM value for all rows, therefore you will see no different formatting from row to row.