Add the average per different category

[madhas]

I have a simple data model as below:

and each table has following values:

Product Table:

Device Number	Design Code
Prod-35282	R6PM06
Prod-35283	S6PM06
Prod-35284	T6PM06
Prod-35285	R6PM06
Prod-35286	T6PM06
Prod-35713	S6PM06
Prod-35714	S6PM08
Prod-35715	R6PM06
Prod-35716	T6PM06
Prod-35717	R6PM06
Prod-35718	T6PM06

Result Table:

Device Number	K Value	Z Value	Date
Prod-35282	1100.464	2.564	2022-08-02
Prod-35283	1031.05	5.521	2022-08-02
Prod-35284	1038.676	-0.722	2022-08-06
Prod-35285	1054.08	-4.753	2022-08-15
Prod-35286	1069.47	2.312	2022-08-19
Prod-35713	575.242	5.673	2022-09-01
Prod-35714	612.123	-5.429	2022-09-12
Prod-35715	593.5057	5.273	2022-09-12
Prod-35716	556.704	0.307	2022-09-15
Prod-35717	593.948	-2.182	2022-09-17
Prod-35718	564.945	3.973	2022-10-25

and Calendar Table:

Date	Year	Month	YearWeek
2022-08-02	2022	8	2022-31
2022-08-02	2022	8	2022-31
2022-08-06	2022	8	2022-31
2022-08-15	2022	8	2022-33
2022-08-19	2022	8	2022-33
2022-09-01	2022	9	2022-35
2022-09-12	2022	9	2022-37
2022-09-12	2022	9	2022-37
2022-09-15	2022	9	2022-37
2022-09-17	2022	9	2022-37
2022-10-25	2022	10	2022-43

what I want to have in my report should look like this:

Device Number	K Value	refK	K value-refK

in which the refK is the average of all K values of the same design code.

I tried the following DAX formula to calculate the refK but it does not work:

refK = 
 VAR __avgKvalue=AVERAGE(Restult Table[K Value])  
 Return
 CALCULATE(
    AVERAGEX(__avgKvalue,
    FILTER(Product Table,
    VAR __CurrentDesignCode=Product Table[Design Code]
    Return
    Product Table[Design Code]=__CurrentDesignCode)
            )
          )

any ideas?

[madhas]

@lbendlin Thanks. It works. May I ask why did you use MAX here? EARLIER was the first thing came to my mind, I used it but did not work. 

[lbendlin]

EARLIER is a leftover from the time before DAX had "variables" (which aren't really variable, but that's another story). MAX was chosen arbitrarily in this scenario, any aggregation would have worked. It all depends on what makes sense for the total.

[madhas]

@lbendlin 

this doesn't deliver the needed report as I described in my post. 

[lbendlin · Accepted solution]

It does, but in a graphical way.  Here's the same as a measure.

[lbendlin]

No need for code  - the data model can do the work.  see attached.