Forum Discussion
Add the average per different category
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?
It does, but in a graphical way. Here's the same as a measure.
5 Replies
No need for code - the data model can do the work. see attached.
It does, but in a graphical way. Here's the same as a measure.
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.
