cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
C-Jac
Helper I
Helper I

Sum of Average by Asset

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. 

C-Jac_0-1622037053157.png

 

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:

AssetValueBroker
151
192
173
241
252
331
342
441
452

 

Relationship:

LoanAsset
11
12
23
34

 

Then what I need is the following: 

 

AssetAverage Value
17
24.5
33.5
44.5

 

And the calculation for the loans should then be: 

LoanValue
111.5
28

 

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 🙂 

 

 

1 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 )
)

MFelix_0-1622204162786.png

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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 )
)

MFelix_0-1622127065868.png

PBIX file attach.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

 

C-Jac_1-1622191516287.png

C-Jac_2-1622191584309.png

C-Jac_3-1622191688261.png

 

 

 

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 )
)

MFelix_0-1622204162786.png

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors