cancel
Showing results for
Did you mean:
Frequent Visitor

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

Dear PowerBI experts!

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

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

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

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

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

2 REPLIES 2
Senior Member

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

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

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

Highlighted
Frequent Visitor

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

Thanks a lot it works!

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 59 members 1,226 guests
Recent signins: