Add Table Column that Counts Records with Criteria According to Row Values

[Flint]

Hello, I have a table as per below and I wish to accomplish 2 changes:

1. Only for records with [Risk] value equal to black, records are always shown for "retired" [Status] selection even if "retired" [Status] not selected for.  For example, all black "retired" [Status] records are shown, but not the ones with "red" [Status] (as per record with strikethrough)
2. Add column ([New Desired Column] to show count of [Market] and [Risk] in table according to row [Market] and [Risk] values.

Please see example table below:

Market, Risk, Project Count, Status, New Desired Column
US, Black, 4, Active, 8
US, Black, 2, Active, 8
US, Black, 2, Retired, 8
US, Red, 3, Active, 1
US, Red, 2, Retired (should not show this record)
CAN, Black, 2, Active, 2
CAN, Red, 3, Active, 3
Mexico, Red, 5, Active, 5

Could you help by providing the expression for the [New Desired Column] measurement?

[vishsaggi]

Your 4th row New column should be 3 right?
Quite did not understand your first requirement can you elaborate with an example from your data sample.

You can try below expression for your 2nd requirement. Like using straight table add your
dimensions then your expr like
=Sum(TOTAL <Market, Risk> [Project Count])

[Flint]

Hi Vishsaggi,

2) You solved my second question - thanks.

1) For my first one, I edited my original post to try and explain better.  To summarize, I wish to update the set analysis so that 'black' records with retired status are always shown even if retired status is not selected.  'red' (or other) records with retired status will not be shown or summed if 'retired' status is not selected for.  Would also like to remove those records with 'N/A' (not black or red) risk.

[Flint · Accepted solution]

For anyone who is having a similar problem to always show selected field values irregardless of user selection, the measurement expression I ended up using for my first question is below (I also wanted to remove "N/A" risk):

Count({<[Status]=[Status]+{"Retired"},
[Risk]=[Risk]-{"N/A"}>}
total <[Market], [Risk]>
if( not([Risk]<>'Complete' and [Status]='Retired') ,[UniqueID]))

I am marking this as the solution as it fulfilled both question parts.  Hope this helps someone out there in the interverse.