topic Total row and column ignore the condition in DAX Commands and Tips

Total row and column ignore the condition

Trodo7377 — Fri, 16 May 2025 12:14:36 GMT

In a matrix I'm using the following measure:

m =SUMX( VALUES('abc'[RRFI_ID]) ,CALCULATE(DISTINCTCOUNT('abc'[ID])) )

It works fine in matrix table and shows correct number in Total row and column. But when I add IF condition as follow:

m = Var _c = SUMX( VALUES('abc'[RRFI_ID]) ,CALCULATE(DISTINCTCOUNT('abc'[ID])) ) Return IF(_c>1, _c, blank())

the Total row and column doesn't show correct number. Somehow the Total row and column ignore the condition in IF statement.

how can I change the measure to get correct number in Total ro and column in matrix?

Re: Total row and column ignore the condition

DataNinja777 — Fri, 16 May 2025 16:04:54 GMT

Hi Trodo7377 ,

The reason your total row in the matrix isn't working as expected is because Power BI evaluates totals using the entire context of the visual, not by simply summing the visible rows. When you use an IF statement that returns BLANK() for certain rows, those blank values are ignored in the total, but the logic inside the IF is not applied the same way at the total level. To ensure the condition _c > 1 is applied both to the individual rows and the total, you need to explicitly apply the filtering logic across all rows using a table expression. Here's how you can rewrite your measure:

m = VAR _table = ADDCOLUMNS( VALUES('abc'[RRFI_ID]), "Count", CALCULATE(DISTINCTCOUNT('abc'[ID])) ) RETURN SUMX( FILTER(_table, [Count] > 1), [Count] )

This measure constructs a table of RRFI_ID values along with their corresponding distinct counts of ID. It then filters that table to keep only the rows where the count is greater than 1 and sums those values. This ensures consistent behavior in both individual rows and total rows in the matrix.

Best regards,

Re: Total row and column ignore the condition

maruthisp — Mon, 19 May 2025 03:35:06 GMT

Hi Trodo7377,

Can you try to go through the attachedpbix file?
Total row and column ignore the condition.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: Total row and column ignore the condition

Trodo7377 — Mon, 19 May 2025 07:23:38 GMT

Thank you DataNinja777 for your reply. I tried it but it doesn't show anything in matrix table.

Re: Total row and column ignore the condition

Trodo7377 — Mon, 19 May 2025 07:35:15 GMT

Thank you maruthisp for your suggestion. The result table(matrix) show nothing and it is empty.

Re: Total row and column ignore the condition

Anonymous — Wed, 28 May 2025 11:42:28 GMT

Hi Trodo7377

Thank you for reaching out to Microsoft Fabric Community.

Please try below measure, it may help you.

m =
SUMX (
VALUES ( 'abc'[RRFI_ID] ),
VAR _c = CALCULATE ( DISTINCTCOUNT ( 'abc'[ID] ) )
RETURN IF ( _c > 1, _c, BLANK () )
)

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Re: Total row and column ignore the condition

Anonymous — Sat, 31 May 2025 11:11:58 GMT

Hi Trodo7377

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Total row and column ignore the condition

v-sgandrathi — Wed, 04 Jun 2025 11:03:15 GMT

Hi Trodo7377,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you.

Re: Total row and column ignore the condition

Anonymous — Sat, 07 Jun 2025 16:41:56 GMT

Hi Trodo7377
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.