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

powerbi_Mandal — Fri, 10 Nov 2023 09:17:35 GMT

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

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

Rupak_bi — Fri, 10 Nov 2023 09:56:12 GMT

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

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

powerbi_Mandal — Fri, 10 Nov 2023 10:24:24 GMT

Thanks!!

topic Re: How to multiply percentage from dimension table with amount from fact table - many to many relation in DAX Commands and Tips

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

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

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