Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
4 years ago
Solved

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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.