Specific Averages Multiple Variables

[awitt]

Hello, 

I'm looking to create calculated columns that are averaging data that is not in that particular row.

For example, the data below has a state, tracking number, shipment type, and cost for each record. What I want to see is that true cost which is already identified compared to the average cost for that shipment type for that state only and only if that shipment type is different. So row 1 would have a Desired Result 1 value of 10 because that was the acutal cost of that shipment type so no average would need to be calculated, but Desired Result 2 would be the average of Shipment Type 2 only in OH (just the average of Shipment # 1002, 1005 and 1006). 

State	Tracking Number	Shipment Type	Cost	Desiered Result 1	Desiered Result 2
OH	1001	1	10	10	3.4
OH	1002	2	3	10.2	3
OH	1003	1	10.5	10.5	3.4
OH	1004	1	10.1	10.1	3.4
OH	1005	2	4	10.2	4
OH	1006	2	3.2	10.2	3.2
TX	1007	1	10	10	5
TX	1008	1	9.8	9.8	5
TX	1009	1	10.5	10.5	5
TX	1010	1	11	11	5
TX	1011	2	4.8	10.1	4.8
TX	1012	2	5.2	10.1	5.2

[v-yalanwu-msft · Accepted solution]

Hi, @awitt ;

You could try to create a measure.

Desiered Result1 = IF(MAX('Table'[Shipment Type])=1,MAX([Cost]),CALCULATE(SUM('Table'[Cost]),ALLEXCEPT('Table','Table'[State],'Table'[Shipment Type])))

Desiered Result2 = IF(MAX('Table'[Shipment Type])=1,
                      CALCULATE(AVERAGE('Table'[Cost]),FILTER( ALL('Table'),[State]=MAX([State])&&[Shipment Type]=2)),
                      MAX([Cost]))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.