Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

utilisation % based on two tables

I have two tables. Table 1 got the ID,Name,Dep,Approved amount and QTR. Table 2 got ID,Name,Dep,Used amount and QTR.

I need a measure that will help me to calculate the utilisation % for each employee.

Formula=Table 2[used amount]/Table 1[Approved amount]

Below is the sample data.

Table 1
IDNameDepApproved_AmountQTR
123RajSales234Qtr 1
234SamPRO345Qtr 1
234SamPRO654Qtr 1
256RexSer345Qtr 1
256RexSer357Qtr 1
123RajSales789Qtr 2
234SamPRO654Qtr 2
234SamPRO890Qtr 2
256RexSer234Qtr 2
256RexSer654Qtr 2

 

Table 2
IDNameDepUsed_AmtQTR
123RajSales234Qtr 1
234SamPRO345Qtr 1
234SamPRO654Qtr 1
256RexSer345Qtr 1
256RexSer357Qtr 1
123RajSales789Qtr 2
234SamPRO654Qtr 2
234SamPRO890Qtr 2
256RexSer234Qtr 2
256RexSer654Qtr 2
Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Create common dimension of Employee and Qtr and then this formula will work across common dimesions

     

    Formula=divide(sum(Table 2[used amount]),sum(Table 1[Approved amount]))

     

    Like

    QTR Dim = distinct(union(all(Table1[QTR]),all(Table2[QTR])))

    If Name and Dept is unique for ID .

    Employee = distinct(union(summarize(table1, table1[ID], table1[Name], table1[Dept]),
    summarize(table2, table2[ID], table2[Name], table2[Dept])))

    You can use selectcolumns in place of summarize