Skip to main content
cancel
Showing results for 
Search instead 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

Reply
powerbi_Mandal
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:

powerbi_Mandal_1-1699607341156.png

 

 

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

powerbi_Mandal_0-1699607146094.png
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

powerbi_Mandal_2-1699607616687.png

 

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
Rupak_bi
Helper V
Helper V

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

View solution in original post

2 REPLIES 2
Rupak_bi
Helper V
Helper V

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

Thanks!! 

powerbi_Mandal_0-1699611804581.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.