Multiply two tables

[baronraghu]

Hi All,

I am trying to determine work allocation based on the information given in the below tables.

Table 1 has three columns 

Table 2

What I want to achieve is when I select like "GA 10.1" on the slicer  then a table must be created which gives how much portion of work will be completed end of each calender Year

like

2018      0

2019      0

2020     1001011*10%*(9/12)= 75076

2021     (1001011*10%*(3/12))+(1001011*20%*(9/12))

and so on

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

Well, I did create an MMULT Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/MMULT/m-p/630231

[baronraghu]

Can you please help me on how to use it in my query

[Greg_Deckler]

If you do it in your query it won't be dynamic. If that is OK, @ImkeF is probably the best person to help out. You could do the same thing MMULT is doing, transpose the 2nd table adn then merge it.

[ImkeF]

I would create an additional column in Table2 where the EffectiveMWP is calculated.

Therefore you first add an Index-column to the table (called "Index" and using the default-settings so that it starts with 0)

Then you add a column with this formula:

[MWP]*0.7 + #"Added Index"[MWP]{[Index]-1}*0.3

Then merge this table on column on Column "Round" with table 1 and expand the new column "EffectiveMWP" and multiply it with the Amount from Table.1. 

You also want to expand the column "Start_Date" and extract its year-portion for the Year-column.