Matrix - display 1 or -1 values based on a text field

[paguy215]

I have a matrix displaying a series of columns, with the last column being a count of unique account numbers (summarizing on countd of those account numers)...So each row should have a 1 in that field.   However, I would like to be able indicate a -1 if something cancelled.   So I have a column that says if something is cancelled and is blank if it isn't.   How can I get that COUNTD of account numbers in that row to be -1 for cancelled and 1 for non-cancelled.  I tried using IFs but they don't work

[Anonymous · Accepted solution]

Hi @paguy215 

Thanks for the reply from @Learner27 , please allow me to provide another insight:

My sample:

If you don't necessarily want to use a calculated column, try creating the following measure:

Measure = IF(MAX([cancelled]) = BLANK(), CALCULATE(DISTINCTCOUNT('Table'[account]), ALLEXCEPT('Table', 'Table'[account])), -1)

Output:

If this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it.

Best Regards,
Yulia Xu

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

[Learner27]

I belive it should work when you have created a calculated column. Could you provide your Sample PBIX file by hiding all the sensitive Data

[Learner27]

Hi  have you tried to create a new calculated column in your data table that assigns 1 for non-cancelled accounts and -1 for cancelled accounts.

[paguy215]

Yes but that doens't work because there are multiple rows for each account ID in some cases ... IE maybe 3 or 4 regular ones plus the one record that's cancelled, so the matrix sums them, showing 3 positive and -1 negative...I would want only one distinct record for positive and for cancelled