Forum Discussion
Create average measure
Good day,
How to calculate average using measure (data source is restricted to add calculated columns) on the measure from one table that will depend on two columns from two different tables.
To better explain, we need an average on a measure that is delta Scheduled Ship-Requested Ship (one table) based plant from backlog table that needs to be combined with a product category from another table. Basically, we have two or more plants having the same product category as a different delta of Scheduled Ship-Requested Ship.
Example,
- Table:
Ship date
Column:
Measure (Requested Ship minus Scheduled Ship data)
- Table:
Backlog,
Column: Plant
- Table:
Parts,
Column: Product Category
Plant | Product Category | Requested Ship - Scheduled Ship Date |
A | Product Category 1 | 128 |
A | Product Category 2 | 128 |
A | Product Category 3 | 107 |
A | Product Category 4 | 105 |
A | Product Category 5 | 79 |
A | Product Category 6 | 69 |
A | Product Category 7 | 65 |
A | Product Category 8 | 63 |
A | Product Category 9 | 59 |
A | Product Category 10 | 41 |
A | Product Category 11 | 26 |
B | Product Category 1 | 174 |
B | Product Category 2 | 156 |
B | Product Category 3 | 136 |
B | Product Category 4 | 127 |
Thank you!
Hi, NDG
Try this:Measure2 = AVERAGEX( FILTER( ALL( 'Table (2)' ), [Manufacturing Plant] = MAX( 'Table (2)'[Manufacturing Plant] ) ), [_Req Ship - Sched Ship] )[_Req Ship - Sched Ship] is a measure now.
_Req Ship - Sched Ship = SUM('Table (2)'[Req Ship - Sched Ship])Result:
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
smpa01 we have 3 different tables where table Ship Date column Requested Ship minus Scheduled Ship date is measure (delta between Scheduled Ship Date and requested ship date). Now, when I need a measure to have an average on this delta that will depend on the Plant and Product Category (2 different tables).
So 2 plants A and B, different product category where we can have same one at the 2 plants and the delta in days,
Hi, NDG
Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?
For example, you have Table 1, Table 2, and Table 3, and you attach the table data, and then you want to get Table 4. This makes it easier for people to understand your problem.
And It would be great if there is a sample file without any sesentive information here.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Good day,
Let’s try to be clearer. I need to get average days for delta Scheduled Ship Date–Requested ship date that needs to be an additional measure.
Now the source to get to this information are 3 tables, from one I get the Manufacturing Plant from other Sales Product Category and last the delta of Ship Date – Requested ship date which is a measure from last one.
I need to have averaged as measure same as example pivot bellow
Average of Req Ship - Sched Ship
Manufacturing Plant
Sales Product Category
Total
ANT
ALUM ELEC AXIAL
19
ALUM ELEC RADIAL
26
ALUM POLY SMD
107
P5 STACKED
133
POWERBOX
112
PULSE
126
RFI FILM RADIAL
119
SINGLE-ENDED E-LYTIC
93
SMD-ELECTROLYTIC
67
SNAP-IN
105
ANT Total
118
BTM
FILM RADIAL
157
P5 STACKED
119
PULSE
136
RFI FILM RADIAL
173
BTM Total
149
EVE
ALUM ELEC AXIAL
38
ALUM ELEC RADIAL
57
SCREW TERMINAL
135
SNAP-IN
236
EVE Total
103
KYU
FILM RADIAL
138
FILM SPECIAL
175
LMC
106
POWER CANS
87
PULSE
118
RFI FILM RADIAL
103
KYU Total
109
MCD
FILM RADIAL
128
FILTERS
202
LMC
252
POWER AXIAL
161
POWER BRICK
191
PULSE
167
RFI FILM RADIAL
129
MCD Total
148
SAS
FILM RADIAL
54
FILM SMD
100
FILM SPECIAL
9
P5 STACKED
49
PFC
72
POWERBOX
100
POWER BRICK
73
POWER CANS
99
SAS Total
92
SSM
FILM RADIAL
27
FILM SMD
45
FILM SPECIAL
-
PAPER OTHER
166
PULSE
57
RC PAPER
67
RFI FILM RADIAL
82
RFI PAPER RADIAL
47
SSM Total
58
Grand Total
120
Can’t load a power bi file or excel as an example, I hope pivot will work.
Thank you and let me know if anything else is needed
Hi, NDG
Try to create a measure like this:Measure 2 = CALCULATE( AVERAGE('Table (2)'[Req Ship - Sched Ship]), ALLEXCEPT('Table (2)','Table (2)'[Manufacturing Plant]))Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
