How to exclude rows from the calculation based on a row value in a table

[ssolovei]

Dear PowerBI experts!

Could you please help me with the following question.

I have two dimensions DimFund and DimStock and one fact table FactInvestmentAmounts. This fact table contains investment amounts for each stock for a given fund. The fact table has the following fields: Fund, Stock, InvestmentAmount:

Fund  Stock  InvestmentAmount

A        S1      $200

A        S2      $800

B        S1      $300

B        S2      $700

B        S3      $1000

C        S1      $100

C        S2      $100

C        S3      $300

Based on the fact table from our example we can calcualate that the average investment amount across all funds is $438

I need to crerate the folowing table for my report:

FundName  averageAmountInvestedExcludingCurrentFund

A                $417

B                 $300

C                 $600

averageAmountInvestedExcludingCurrentFund is a measure that calcultes avearge investment amount across all funds excluding investments from the fund in the current row.

Is it possible to create such a measure? I have tried a lot of different approaches but still have not found the solution yet.

Thanks

[mattbrice · Accepted solution]

Assumming you have DimFund[FundName] on the rows, you can try this:

averageAmountInvestedExcludingCurrentFund=
CALCULATE (
    AVERAGE ( FactTAble[InvestmentAmount] ),
    EXCEPT ( ALL ( DimFund[FundName] ), VALUES ( DimFund[FundName] ) )
)

[ssolovei]

Thanks a lot it works!