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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 158 members 2,001 guests
Please welcome our newest community members: