Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Column Calculations in Excel vs in Power BI

I've been asked to put together a Power BI dashboard using an Excel file as the data source.

I noticed the Excel workbook has around 15 columns which use various formulas to arrive at a calculation for that column. I have a pretty quick turnaround on this, so I may have to rely on the Excel formulas as I'm just getting started with DAX and don't want that to hold me up.

 

Should be noted that this solution is temporary until an ETL process is completed at which point the Power BI dashboard will source the data from a database.

 

My questions are:

  1. Is best practice to perform calculations in Excel or in Power BI?

  2. Is there a significant disadvantage to performing the calculations in Excel?

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Anonymous ,

     

    I'd file this question firmly in the "it depends" folder.

    Assuming that this was a production-standard piece of work that wasn't going to be imminently superseded, and that the raw source data was always going to remain in Excel, I'd say the following:

     

    --1--

    All in Power BI. You're going to want everything about that report in one place i.e. just raw data in Excel, then everything else in either Power Query or DAX measures. This means there's essentially only one point of maintenance/failure (the PBIX), and it's more closely controlled and validated.

     

    --2--

    See point 1.

     

    However, as this is only a temporary solution and, by your own admission, you're just getting started with DAX, I'd say just keep whatever is in Excel for the sake of speed and simplicity. It's unlikely you'll see any performance, compatibility, or validation impacts in the short-term.

     

    Once you move your raw data source to a DB, my point 1 above will change slightly. At this point, you may want to actually transfer some of the Power Query load to your DB via views or similar but, again, this will also be a closely controlled and validated environment.

     

    Pete