Sum of Average by Asset

[C-Jac]

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 🙂 

[MFelix]

Hi @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.

[C-Jac]

It 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. 

[MFelix · Accepted 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.