Change a category based on total value

[gg51]

Here is my problem. I have a table that have the applications/Admits/registations

it has the following stucture:

 ID, year, Application, Admits, Registration, faculty, department, program, status of student, country of origin, etc...

Application, Admits, Registration may have the value 1 or 0

I have a matrix that show

Rows: status of student, country of origin,

Column: year 

Value:  SUM(registration) 

We notice that for certain country, there is only 1 registration. For absolute anonymity, we would like that if the TOTAL > 5, the country of origin became "Others".

How would you go about it? I have created a summarized table and join, but it ignore any other filters that could be apply (faculty for example) because the new table does not take into account the filter context.

Thanks in Advance

Gérard Garreau

[v-yadongf-msft · Accepted solution]

Hi @gg51 ,

This is my test table:

Create a new column:

New_country of origin = 
var _sum = CALCULATE(SUM('Table'[Registration]),FILTER('Table','Table'[Status of student] = EARLIER('Table'[Status of student]) && 'Table'[Country of origin] = EARLIER('Table'[Country of origin]) && 'Table'[Year] = EARLIER('Table'[Year])))
return
IF(_sum > 5,"Others",'Table'[Country of origin])

Is this the result you want:

Best regards,

Yadong Fang

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