Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |