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.
I have a fact table with valuations of our assets, we get approximately three valuations of each asset per quarter.
Then I have a dimension table with all the assets, so the valuations and the assets are related through this.
Then I have a fact with our loans.
Through a many to many relationship table the loans and the assets are connected.
The relationships can be seen in picture below.
What I need, is a calculation for each asset, which averages the broker valuations of this asset, and THEN summarizes the valuation. So if I need to see the total value of the assets connected to a certain loan, then it wil be the sum of the three values, e.g.:
Valuation:
Asset | Value | Broker |
1 | 5 | 1 |
1 | 9 | 2 |
1 | 7 | 3 |
2 | 4 | 1 |
2 | 5 | 2 |
3 | 3 | 1 |
3 | 4 | 2 |
4 | 4 | 1 |
4 | 5 | 2 |
Relationship:
Loan | Asset |
1 | 1 |
1 | 2 |
2 | 3 |
3 | 4 |
Then what I need is the following:
Asset | Average Value |
1 | 7 |
2 | 4.5 |
3 | 3.5 |
4 | 4.5 |
And the calculation for the loans should then be:
Loan | Value |
1 | 11.5 |
2 | 8 |
So dependent on the granularity I need to be able to drill down and say, for loan 1, what are the average values for the asset, as well as for loan 2. And I should naturally be able to see the value development for each valuation date.
Hope this makes sense 🙂
Solved! Go to Solution.
Hi @C-Jac ,
That is related with the context of the calculations when you add more columns to your visualization the context changes, in this case you have loan, asset and Valuation Date, you need to add the valuation date to the summarize should be something similar to this:
Sum of averages valuation =
CALCULATE (
SUMX (
SUMMARIZE (
Valuation,
dimasset[Asset],
Valuation[valuationdate],
"average", AVERAGE ( Valuation[Value] )
),
[average]
),
CROSSFILTER ( dimasset[Asset], AssetLoan[Asset], BOTH )
)
Be aware that you need to make two measures depeding on the context or make a change in the overal to have a IF statment to change context.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @C-Jac ,
Try the following measure:
0
Sum of averages valuation =
CALCULATE (
SUMX (
SUMMARIZE (
Valuation,
dimasset[Asset],
"average", AVERAGE ( Valuation[Value] )
),
[average]
),
CROSSFILTER ( dimasset[Asset], AssetLoan[Asset], BOTH )
)
PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIt mainly works, only showing the average value instead of the sum on each asset line, but when the loan is the only column and the assets aren't there, then it doesn't sum, it gives me the average at the bottom, and it seems like it cannot choose only the assets attached to the specific loan, but it takes in ALL the assets and attaches them to each loan.
For the attached loan selected in the pictures below, there are only 4 assets that belongs to the loan, and when they are selected, in each row it gives the correct average value, but does not SUM it at the bottom.
Hi @C-Jac ,
That is related with the context of the calculations when you add more columns to your visualization the context changes, in this case you have loan, asset and Valuation Date, you need to add the valuation date to the summarize should be something similar to this:
Sum of averages valuation =
CALCULATE (
SUMX (
SUMMARIZE (
Valuation,
dimasset[Asset],
Valuation[valuationdate],
"average", AVERAGE ( Valuation[Value] )
),
[average]
),
CROSSFILTER ( dimasset[Asset], AssetLoan[Asset], BOTH )
)
Be aware that you need to make two measures depeding on the context or make a change in the overal to have a IF statment to change context.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |