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
Highlighted
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
Highlighted
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!

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 324 members 3,053 guests
Please welcome our newest community members: