Forum Discussion

ssolovei's avatar
ssolovei
Frequent Visitor
8 years ago
Solved

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

 

Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

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

     

     

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