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 inves...
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] ) )
)

     

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

 

ssolovei's avatar
ssolovei
Frequent Visitor
8 years ago

Thanks a lot it works!