cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Super User

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

5 REPLIES 5
Frequent Visitor

@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.

Super User

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.

Frequent Visitor

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

Super User

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

Super User

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors