none
Need to calculate metrics

    Question

  • Hi,<o:p></o:p>

    I am having PWA data in MS excel in different sheets like Project, Task,Assignment and Resource. so i want to calculate metrics using few columns from different sheets.<o:p></o:p>

    Is it possible to add multiple columns from different sheets in single expression in power pivot?<o:p></o:p>

    please help me in this.<o:p></o:p>

    Thanks in Advance<o:p></o:p>

    Sarath.<o:p></o:p>



    Wednesday, July 23, 2014 11:41 AM

Answers

  • Hi Sarath,

    Yes, it is possible to use data from different sheets in power pivot in a single expressions.

    What you probably want to do is to import those sheets that are used a the data source as linked tables into power pivot - it is explained here.

    Each sheet will correspond to a single table in Power Pivot. Depending on your data model you may add relationships between your tables. This actually entirely depends on your specific situation - the same is valid for the definition of your metrics.

    I would recommend reading about Power Pivot - powerpivotpro.com is a very good resource.

    Regards,

    Julian 


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Wednesday, July 23, 2014 1:12 PM
  • You're getting this error because of the ending portion of your IF() function. The error message in this case is very helpful:

    The value for column 'AssignmentBaselineWork' in table 'AssignmentBaselines' cannot be determined in the current context. 

    when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

    One of these two sections is the cause of your problem.

    In the first case, you either do not have relationships appropriately defined between the tables and the Power Pivot engine cannot figure out which value you want.

    In the second case, there are multiple possible values of 'AssignmentBaselines'[AssignmentBaselineWork] and you need to wrap that reference in an aggregation function. 

    If you could provide us a data sample including an example of expected results, that would be most helpful.

    Thursday, July 24, 2014 2:32 PM

All replies

  • Hi Sarath,

    Yes, it is possible to use data from different sheets in power pivot in a single expressions.

    What you probably want to do is to import those sheets that are used a the data source as linked tables into power pivot - it is explained here.

    Each sheet will correspond to a single table in Power Pivot. Depending on your data model you may add relationships between your tables. This actually entirely depends on your specific situation - the same is valid for the definition of your metrics.

    I would recommend reading about Power Pivot - powerpivotpro.com is a very good resource.

    Regards,

    Julian 


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Wednesday, July 23, 2014 1:12 PM
  • Hi Julian,

    Thanks for your reply. i will check your references and get back to you.

    Regards,

    Sarath,

    Wednesday, July 23, 2014 1:25 PM
  • Hi Julian,

    I am trying to write below expression but i am getting below error. please help me in this.

    =IF(Tasks[TaskActualFinishDate]<="22-07-2012",0,AssignmentBaselines[AssignmentBaselineWork])

    ERROR:

    Calculation error in measure 'AssignmentBaselines'[Calculated field 1]: The value for column 'AssignmentBaselineWork' in table 'AssignmentBaselines' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

    Thursday, July 24, 2014 8:48 AM
  • You're getting this error because of the ending portion of your IF() function. The error message in this case is very helpful:

    The value for column 'AssignmentBaselineWork' in table 'AssignmentBaselines' cannot be determined in the current context. 

    when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

    One of these two sections is the cause of your problem.

    In the first case, you either do not have relationships appropriately defined between the tables and the Power Pivot engine cannot figure out which value you want.

    In the second case, there are multiple possible values of 'AssignmentBaselines'[AssignmentBaselineWork] and you need to wrap that reference in an aggregation function. 

    If you could provide us a data sample including an example of expected results, that would be most helpful.

    Thursday, July 24, 2014 2:32 PM
  • Hi Julian,

    Here i am providing the sample data. calculted value's are expected results and i defined

    TaskActulaFinishDate Statudate AssinmentBaselinework Calculated Value
    04-08-2013 02-02-2014       58 58
    03-03-2014 02-02-2014       65 0
    08-09-2013 02-02-2014       23 23
    01-01-2014 02-02-2014       34 34
    02-03-2014 02-02-2014         9 0
    23-03-2014 02-02-2014        26 0

    Plese help me in this

    Thanks,

    Sarath.

    Friday, July 25, 2014 7:21 AM
  • Expression for above calculation

    =If(taskActualFinishDate<=StatusDate,AssignmantBaseLinework,0)

    Friday, July 25, 2014 9:04 AM