How to multiply percentage from dimension table with amount from fact table - many to many relation

[powerbi_Mandal]

Hi,

I have a problem getting the appropriate data from my DAX formula. 

Here's my model:

fact
dimBank
dimSecurites
Fact table is a day to day table with debt amortization plan ordered by tranche (%dimSecurities)

dimSecurities is a table with all specific data for the vessel, each having unique key %dimSecurities

dimBank is a table where the split between multiple banks, where a %dimSeucrities sometimes occur 3-4 times depending on how many banks involved
This is a simplified version of my data model:

Looking at this table, the data is correct when I add other dimensions, like "Share" or even better "%dimSecurities", but the total sum is incorrect as it is a result of the average share multiplied by total debt... 

However, I would like to show these data graphical in a barchart, but then I cannot add the aggregation, hence the total sum is being used, wich is incorrect, 12,017,545 when the correct sum should be more like 7,961..... etc

This is my DAX formula... 

debt by lenders = CALCULATE(SUMX('factSecurity',factSecurity[Loan Balance Start] * LOOKUPVALUE(dimT16_Valutakurs[Currency Rate], dimT16_Valutakurs[Dates], Max('Calendar'[Date]), dimT16_Valutakurs[FRA_VAL_KODE], factSecurity[Currency])), FILTER(factSecurity, factSecurity[Dates] = Max('Calendar'[Date])))* AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share])

Any help is much appreciated 🙂

Thanks 
E

[Rupak_bi · Accepted solution]

To get correct sum, you need to do the caculation in column, not in measure. column sum will work as expected.

[powerbi_Mandal]

Thanks!!