Total row and column ignore the condition

[Trodo7377]

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?

[v-priyankata]

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.

[v-priyankata]

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.

[v-sgandrathi]

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.

[v-priyankata · Accepted solution]

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.

[maruthisp]

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

[Trodo7377]

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

[DataNinja777]

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,

[Trodo7377]

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