Forum Discussion

dconlon's avatar
dconlon
New Member
6 years ago
Solved

Create dynamic table from two tabels

HI there.

 

I have the following tables:

All_Data

 

 

Rates_Data

 

I am looking to create the following table:

Variance_Data

 

 

 

 

 

 

 

Any help is greatly appreciated!

 

donal

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi dconlon 

    Create columns

    In Table 1(Rates data)

    Column1 = [project]&"-"&[client]&"-"&[wbs]&"-"&[teammate id]

    In Table2(all data)

    Column2 = [project]&"-"&[client]&"-"&[code]&"-"&[teammate name]

    Create relationship between two tables based on column1 and column2

     

    Then create columns in Table1

    actual hours = CALCULATE(SUM(Table2[hours]),ALLEXCEPT(Table1,Table1[Column1]))

Column=[allocated hours]-[actual hours]
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
     

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi dconlon 

    Create columns

    In Table 1(Rates data)

    Column1 = [project]&"-"&[client]&"-"&[wbs]&"-"&[teammate id]

    In Table2(all data)

    Column2 = [project]&"-"&[client]&"-"&[code]&"-"&[teammate name]

    Create relationship between two tables based on column1 and column2

     

    Then create columns in Table1

    actual hours = CALCULATE(SUM(Table2[hours]),ALLEXCEPT(Table1,Table1[Column1]))

Column=[allocated hours]-[actual hours]
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.