cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Helper V

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

2 REPLIES 2
Helper V

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

Frequent Visitor

Thanks!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors