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

 

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

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

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: 292 members 2,711 guests
Please welcome our newest community members: