Measure that calculates subtotal in matrix and also works in pie chart

[user01]

I have two tables Reference and Table. I bring them in by DirectQuery. I have a measure that does some calculations. I've simplified the tables and measures for convenience/illustration. When I put FinalMeasure in a matrix, it calculates everything correctly at the SubCategory level. 

Goals:

1. I want to display the subtotals at the Category level as well.

2. I want to be able to put this measure in a pie chart to look at the pie at either the SubCategory level or the Category level.

How can I accomplish this?

FactorMeasure = 1 

FinalMeasure = 
var _subcat = SELECTEDVALUE('Table'[SubCategory])
var _var = SELECTEDVALUE('Table'[Variable])
var _factor = [FactorMeasure]
var _amt = LOOKUPVALUE(Reference[Amount], Reference[Variable], _var)
var _amtc3 = LOOKUPVALUE(Reference[Amount], Reference[Variable], "_C3_")
var _calc = SWITCH(_subcat,
    "A1", _amt + 1,
    "A2", _amt + 2,
    "B", _amt + 3,
    "C1", _amt + 4,
    "C2", _amt + 5,
    BLANK()
)*_factor
return SUMX('Table', IF(_subcat = "C3", _amtc3, _calc))

Current Results:

 Desired Results (Excel example):

Table:

Category	SubCategory	Variable	Key
A	A1	_A1_	1
A	A2	_A2_	2
B	B	_B_	3
C	C1	_C1_	4
C	C2	_C2_	5
C	C3	_C3_	6

Reference:

Variable	Amount
_A1_	30200
_A2_	5500
_B_	17700
_C1_	3600
_C2_	18100
_C3_	800

[Anonymous · Accepted solution]

Hi, @user01 

Since both tables have a relationship, we just need to create a measure that uses the sum function.

FinalMeasure 2 = SUM('Reference'[Amount])

Here is my preview

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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