cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssolovei Frequent Visitor
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Senior Member
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
mattbrice Senior Member
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] ) )
)

 

ssolovei Frequent Visitor
Frequent Visitor

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

Thanks a lot it works!