Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Assumming you have DimFund[FundName] on the rows, you can try this:
averageAmountInvestedExcludingCurrentFund= CALCULATE ( AVERAGE ( FactTAble[InvestmentAmount] ), EXCEPT ( ALL ( DimFund[FundName] ), VALUES ( DimFund[FundName] ) ) )
Assumming you have DimFund[FundName] on the rows, you can try this:
averageAmountInvestedExcludingCurrentFund= CALCULATE ( AVERAGE ( FactTAble[InvestmentAmount] ), EXCEPT ( ALL ( DimFund[FundName] ), VALUES ( DimFund[FundName] ) ) )
Thanks a lot it works!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |