Forum Discussion

baronraghu's avatar
baronraghu
Helper III
7 years ago
Solved

Multiply two tables

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 selec...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

  • baronraghu's avatar
    baronraghu
    Helper III
    7 years ago

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      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's avatar
        ImkeF
        Community Champion
        7 years ago

        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.