Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
awitt
Helper III
Helper III

Specific Averages Multiple Variables

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

 

StateTracking NumberShipment TypeCostDesiered Result 1Desiered Result 2
OH1001110103.4
OH10022310.23
OH1003110.510.53.4
OH1004110.110.13.4
OH10052410.24
OH100623.210.23.2
TX1007110105
TX100819.89.85
TX1009110.510.55
TX1010111115
TX101124.810.14.8
TX101225.210.15.2
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1647234823493.png

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.

View solution in original post

1 REPLY 1
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1647234823493.png

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.