Create average measure

[NDG]

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,

1. Table:

    Ship date

    Column:

    Measure (Requested Ship minus Scheduled Ship data)

1. Table:

    Backlog,

    Column: Plant

1. 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!

[v-angzheng-msft]

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.

[NDG]

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

[v-angzheng-msft]

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.

[NDG]

Thanks, @v-angzheng-msft, can't use AVERAGE as Req Ship -Scheduled Ship is a measure, power bi doesn't let me.

[v-angzheng-msft · Accepted solution]

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.

[NDG]

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

[smpa01]

@NDG  can't understand this sample data.