Multiply Data in One Table by a Percentage from Another Table

[racer25]

Hi 

I have two table FIS_FEEINCOMESPLIT (Fee Income) c 10,000 rows and CAC_CKUEBTACCIYBTUBG (Ckuebt Acciybtubg c 2,500,000 rows.

Within Fee Income is the Departmental Information so  I can have many rows for one invoice but I do have a percentage column which tells me the percentage per department.

Within the  Client Accounting table I have some elements I need such as  (Net, Tax, Gross, Currently, Comment).

I am a Qlikview Developer and ordinarily I would left join where exists what I need from CLient Accounting but so far it PBI it freezing at the end of the load.

I am new to PBI so looking for the best way of doing this. Mt relationship is Many to One.

Thanks in advance

Rob

[Elena_Kalina]

Hi @racer25 

Merging two large tables (10K and 2.5M rows) in Power Query is a bad idea. The proper approach is to use table relationships and DAX measures instead.

1. Keep Tables Separate

2. Create a Proper Relationship

In Model view:

1. Identify the common key field (e.g., InvoiceID)

2. Drag from the smaller table (FIS_FEEINCOMESPLIT) to the larger table (CAC_CKUEBTACCIYBTUBG)

3. Set as a one-to-many relationship (1:*) where FIS_FEEINCOMESPLIT is on the "one" side

3. Create Efficient DAX Measures

// Net Amount with department percentage allocation
Net Amount = 
SUMX(
    FIS_FEEINCOMESPLIT,
    RELATED(CAC_CKUEBTACCIYBTUBG[Net]) * FIS_FEEINCOMESPLIT[Percentage]
)

// Tax with allocation
Tax Amount = 
SUMX(
    FIS_FEEINCOMESPLIT,
    RELATED(CAC_CKUEBTACCIYBTUBG[Tax]) * FIS_FEEINCOMESPLIT[Percentage]
)

// Gross with allocation
Gross Amount = 
SUMX(
    FIS_FEEINCOMESPLIT,
    RELATED(CAC_CKUEBTACCIYBTUBG[Gross]) * FIS_FEEINCOMESPLIT[Percentage]
)

[burakkaragoz · Accepted solution]

Hi @racer25 ,

Don’t try to merge those two big tables in Power Query — it’ll freeze Power BI.

Instead, keep them separate and use a relationship (Many-to-One from Fee Income to Client Accounting). Then create a measure like this:

Net Allocated = 
SUMX(
    FIS_FEEINCOMESPLIT,
    RELATED(CAC_CKUEBTACCITYBTUBG[Net]) * FIS_FEEINCOMESPLIT[Percentage]
)

This way you multiply Net by the department percentage without heavy joins. Much faster and scalable.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.