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

Community Highlights

Find out what's new in the Power BI Community!

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 324 members 3,053 guests
Recent signins: