Measure using AVERAGEX - total doesn't work with legend in bar chart

[pure_designer]

I have a measure for a mean monthly average. It seems to work quite well, except for a case where I break a bar chart up by category and the categories don't all have the same number of months. 

As you can see, the total is the sum of the categories and is not acting like a measure (ignoring the "name") as intended - the graph on the right should have a total mean monthly average of 114.

I have a sample file, not sure how to insert it... so the relevant table is:

PERIOD	user_id	name	value
2021-04-01	1	CAT_A	20
2021-04-01	1	CAT_B	25
2021-04-01	1	CAT_C	60
2021-05-01	1	CAT_A	20
2021-05-01	1	CAT_B	25
2021-05-01	1	CAT_C	60
2021-06-01	1	CAT_A	20
2021-06-01	1	CAT_B	25
2021-06-01	1	CAT_C	60
2021-07-01	1	CAT_A	20
2021-07-01	1	CAT_B	25
2021-07-01	1	CAT_C	60
2021-08-01	1	CAT_A	20
2021-08-01	1	CAT_B	25
2021-08-01	1	CAT_C	60
2021-09-01	1	CAT_A	20
2021-09-01	1	CAT_B	25
2021-09-01	1	CAT_C	60
2021-10-01	1	CAT_A	20
2021-10-01	1	CAT_B	25
2021-10-01	1	CAT_C	60
2021-11-01	1	CAT_A	20
2021-11-01	1	CAT_B	25
2021-11-01	1	CAT_C	30
2021-11-01	3	CAT_C	30
2021-12-01	1	CAT_A	20
2021-12-01	1	CAT_B	25
2021-12-01	1	CAT_C	30
2021-12-01	3	CAT_C	30
2022-01-01	1	CAT_A	20
2022-01-01	1	CAT_B	25
2022-01-01	1	CAT_C	30
2022-01-01	3	CAT_C	30
2022-02-01	1	CAT_A	20
2022-02-01	1	CAT_B	25
2022-02-01	1	CAT_C	30
2022-02-01	3	CAT_C	30
2022-03-01	1	CAT_A	20
2022-03-01	1	CAT_B	25
2022-03-01	1	CAT_C	30
2022-03-01	3	CAT_C	30
2022-03-01	2	CAT_A	8
2022-03-01	2	CAT_B	16
2022-03-01	2	CAT_C	60
2022-03-01	2	CAT_D	18

and the code is:

Total Value = sum([value])

Mean Monthly = AVERAGEX(VALUES('sample'[PERIOD]), [Total Value])

[AlexisOlson · Accepted solution]

Look @Greg_Deckler, the stacked column total is broken!

@pure_designer, the total for a stacked column chart is forced to be a simple sum of the height of each component. Unlike with a matrix or a table, there's no simple workaround since the visual assumes your measure is additive even if it shouldn't be, so I wouldn't recommend using a stacked bar chart for non-additive measures.

I would definitely support an idea for Microsoft to fix this broken visual so that we can choose whether we want a simple total or a measure-based total, rather than being forced into something with no workaround.

[pure_designer]

Thanks, good to know!