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
Highlighted
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
Highlighted
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] ) )
)```

Frequent Visitor

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

Thanks a lot it works!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 158 members 2,001 guests
Recent signins: