TLDR: You need to set sort=False in your groupby, and you need to change your Categorical to a CategoricalIndex. Here's the full working example:

df = pd.DataFrame(d)
grp_cols = ['Card', 'Year']
ser_val = df.groupby(grp_cols, sort=False)['Value'].mean()

categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
categories = pd.CategoricalIndex(ser_val.index.levels[1].values,
                                 categories=categories_order,
                                 ordered=True)
ser_val.index.set_levels(categories, level='Year', inplace=True)
ser_val.sort_index(inplace=True)

And ser_val is now:

Card         Year 
Master Card  One      2401.000000
             Two       321.000000
             Three     146.833333
             Four      755.000000
             Five      294.000000
             Seven     505.000000
Visa         Three      84.500000
             Six        15.000000
             Seven     438.166667
Name: Value, dtype: float64

Longer Ramblings: You ask why what you did doesn't work, which I certainly cannot explain (I'm sure that involves some rooting around in the source), but here is how I arrived at my solution.

See that the following example works, constructing a toy MultiIndex Series from scratch:

lets = ['a','b','c']*3
ids = ['MALE']*4 + ['FEMALE']*5
s = pd.Series(range(9), index=[ids,lets])

categories_order = ['b','a','c']
categories = pd.CategoricalIndex(s.index.levels[1].values,
                                 categories=categories_order,
                                 ordered=True)
s.index.set_levels(categories, level=1,inplace=True)
s.sort_index(inplace=True)

s is sorted as we want:

FEMALE  b    4
        b    7
        a    6
        c    5
        c    8
MALE    b    1
        a    0
        a    3
        c    2
dtype: int64

The only significant difference between your example and mine (that I could tell) is that yours starts from groupby. There is a sort parameter of groupby:

sort : bool, default True Sort group keys. Get better performance by turning this off. Note this does not influence the order of observations within each group. Groupby preserves the order of rows within each group.

So it seems like the groupby sort is enforcing some order that is not being overridden by your new categorical order.

But still with sort=False, your code as is didn't work. Just through googling I found that there were separate classes for Categorical and CategoricalIndex, and apparently the latter is what you need here. And sure enough my example will also fail if Categorical is used instead of CategoricalIndex.

So the groupby thing seems like the more curious issue; again the underlying rules here I can't tell you, but maybe someone can elaborate.

Actually, I think you found a bug or two!

Bug #1 - Changing dtype with set_levels using pd.Categorical doesn't work.

import pandas as pd 
import numpy as np

d = {
    'Card': [
        'Visa', 'Visa', 'Master Card', 'Master Card', 'Visa', 'Master Card',
        'Visa', 'Visa', 'Master Card', 'Visa', 'Master Card', 'Visa', 'Visa',
        'Master Card', 'Master Card', 'Visa', 'Master Card', 'Visa', 'Visa',
        'Master Card', 'Visa', 'Master Card', 'Master Card', 'Master Card',
        'Master Card', 'Master Card', 'Master Card', 'Visa', 'Visa'
    ],
    'Year': [
        'Three', 'Three', 'Seven', 'Three', 'Three', 'Seven', 'Seven', 'Seven',
        'Three', 'Seven', 'Three', 'Three', 'Three', 'Seven', 'Three', 'Three',
        'Seven', 'Seven', 'Seven', 'Three', 'Seven', 'Three', 'Five', 'One',
        'One', 'Two', 'Four', 'Six', 'Six'
    ],
    'Value': [
        45, 13, 52, 321, 31, 1231, 876, 231, 4, 213, 123, 45, 321, 1, 123, 52,
        736, 35, 900, 301, 374, 9, 294, 337, 4465, 321, 755, 22, 8
    ]
}

df = pd.DataFrame(d)
grp_cols = ['Card', 'Year']
ser_val = df.groupby(grp_cols)['Value'].mean()

#---------------------------------------------
#Attempt 1st
categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
categories = pd.Categorical(ser_val.index.levels[1].values,
                            categories=categories_order,
                            ordered=True)
ser_val.index.set_levels(categories, level=1, inplace=True)
print(ser_val.index.levels[1].dtype)

#--------------------------------------------
#Attempt 2nd
categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
categories = pd.Categorical(ser_val.index.levels[1].values,
                            categories=categories_order,
                            ordered=True)
ser_val.index = ser_val.index.set_levels(categories, level='Year')
print(ser_val.index.levels[1].dtype)

#----------------------------------
#Attempt 3rd and success
categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
categoriesDtype = pd.CategoricalDtype(categories_order, ordered=True)
ser_val.index = ser_val.index.set_levels(ser_val.index.levels[1].astype(categoriesDtype), level='Year')
print(ser_val.index.levels[1].dtype)

Output:

object  *FAILED change type using inplace*
object  *FAILED change type using reassignment*
category  *SUCCESS change type using pd.CategoricalDtype*

Bug #2 - sort_index using categorical for MultiIndex level 1 doesn't work

This may be found already here as an open issue #24271

After a successful change of dype for index level 1:

 ser_val.index.levels[1]

Output:

CategoricalIndex(['Five', 'Four', 'One', 'Seven', 'Six', 'Three', 'Two'], categories=['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'], ordered=True, name='Year', dtype='category')

Now, let's sort the dataframe using sort_index:

ser_val.sort_index()

Output (FAIL):

Card         Year 
Master Card  Five      294.000000
             Four      755.000000
             One      2401.000000
             Seven     505.000000
             Three     146.833333
             Two       321.000000
Visa         Seven     438.166667
             Six        15.000000
             Three      84.500000
Name: Value, dtype: float64

Now, just for kicks and testing, let's swap index levels and try sort_index again.

ser_val.swaplevel(0,1).sort_index()

Output(SUCCESS):

Year   Card       
One    Master Card    2401.000000
Two    Master Card     321.000000
Three  Master Card     146.833333
       Visa             84.500000
Four   Master Card     755.000000
Five   Master Card     294.000000
Six    Visa             15.000000
Seven  Master Card     505.000000
       Visa            438.166667
Name: Value, dtype: float64

However, if we explicitly set the sorting levels... FAIL again.

ser_val.swaplevel(0,1).sort_index(level=[0,1])

Output:

Year   Card       
Five   Master Card     294.000000
Four   Master Card     755.000000
One    Master Card    2401.000000
Seven  Master Card     505.000000
       Visa            438.166667
Six    Visa             15.000000
Three  Master Card     146.833333
       Visa             84.500000
Two    Master Card     321.000000
Name: Value, dtype: float64

Using a sort command like sort_index() or groupby(), which includes a sort argument, in earlier steps causes Pandas to have sorting problems. Categorical index sorting fails with lexicographically ordered data. When an index is lexicographically sorted, attempts to sort it by category will fail. Several tricks can be used to solve this problem in these cases.

1. Avoid using commands that sort the index lexicographically. lexicographically ordered data cannot be sorted using a categorical index. In this case, the problem will be solved if the value of the sort argument is set to False when using groupby(). Note that when the sort argument is not set to false, after converting the level_1 index's type to categorical, you will see that by executing the print(ser_val.index.levels[1]), the categorical data values are ordered lexicographically.

   ser_val = df.groupby(grp_cols)['Value'].mean()
   categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
   categories = pd.CategoricalIndex(ser_val.index.levels[1],
                            categories=categories_order,
                            ordered=True)
   ser_val.index = ser_val.index.set_levels(categories, level='Year')
   print(ser_val.index.levels[1])
   
   output:
   
   CategoricalIndex(['Five', 'Four', 'One', 'Seven', 'Six', 'Three', 'Two'], categories=['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'], ordered=True, dtype='category', name='Year')
   

   You can see that ['Five', 'Four', 'One', 'Seven', 'Six', 'Three', 'Two'] is lexicographically ordered.

   But when sort=False:

   ser_val = df.groupby(grp_cols,sort=False)['Value'].mean()
   categories_order = ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven']
   categories = pd.CategoricalIndex(ser_val.index.levels[1],
                            categories=categories_order,
                            ordered=True)
   ser_val.index = ser_val.index.set_levels(categories, level='Year')
   print(ser_val.index.levels[1])
   
   output:
   
   CategoricalIndex(['Three', 'Seven', 'Five', 'One', 'Two', 'Four', 'Six'], categories=['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'], ordered=True, dtype='category', name='Year')
   

   You can see that ['Three', 'Seven', 'Five', 'One', 'Two', 'Four', 'Six'] is not lexicographically ordered and so pandas can sort it without any problem.

   categories = pd.CategoricalIndex(ser_val.index.levels[1],
                            categories=categories_order,
                            ordered=True)
   ser_val.index = ser_val.index.set_levels(categories, level='Year')
   ser_val.sort_index()
   
   output:
   
   Card         Year 
   Master Card  One      2401.000000
                Two       321.000000
                Three     146.833333
                Four      755.000000
                Five      294.000000
                Seven     505.000000
   Visa         Three      84.500000
                Six        15.000000
                Seven     438.166667
   

2. You can first reset the desired index level using reset_index(). Then you can make a CategoricalIndex and use it to categorize the column. Generally, if the data type is converted to categorical before set_index(), there is no sorting problem.Like what you did.

   df_val = ser_val.reset_index().sort_values(grp_cols)
   df_val['Year'] = pd.CategoricalIndex(df_val['Year'],
                           categories_order,
                           ordered=True)
   df_val = df_val.sort_values(grp_cols).set_index(grp_cols)
   print(df_val.index.levels[1])
   df_val
   
   output:
   
   CategoricalIndex(['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'], categories=['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'], ordered=True, dtype='category', name='Year')
   
   Card         Year 
   Master Card  One      2401.000000
                Two       321.000000
                Three     146.833333
                Four      755.000000
                Five      294.000000
                Seven     505.000000
   Visa         Three      84.500000
                Six        15.000000
                Seven     438.166667
   

   Take note of the order of ['One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven'].

3. If you don't use sort=False in groupby(), you can first create a CategoricalIndex (using CategoricalIndex or pd.CategoricalDtype)and assign it to the level_1 of the MultiIndex. Afterward, you need to eliminate the lexicographical sort from the one-level index. You can use two swaplevel() -> sort_index() -> swaplevel() here as a trick.

   categoriesDtype = pd.CategoricalDtype(categories_order, ordered=True)
   ser_val.index = ser_val.index.set_levels(ser_val.index.levels[1].astype(categoriesDtype), level=1)
   ser_val.swaplevel(1, 0).sort_index(level=[1,0]).swaplevel(1, 0)
   
   # Or using:
   # categories = pd.CategoricalIndex(ser_val.index.levels[1].values,
   #                                  categories=categories_order,
   #                                  ordered=True)
   # ser_val.index = ser_val.index.set_levels(categories, level='Year')
   # ser_val.swaplevel(1, 0).sort_index(level=[1,0]).swaplevel(1, 0)
   
   output:
   
   
   Card         Year 
   Master Card  One      2401.000000
                Two       321.000000
                Three     146.833333
                Four      755.000000
                Five      294.000000
                Seven     505.000000
   Visa         Three      84.500000
                Six        15.000000
                Seven     438.166667