Forum Discussion

gg51's avatar
gg51
Regular Visitor
3 years ago
Solved

Change a category based on total value

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

 

 

 

 

 

 

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.