Modelling data in Power BI or Excel/Power Pivot

[Anonymous]

Hi Experts,

I have been learning power query, power pivot and power BI. I’m now trying to tie everything together. The end game is I want to publish a Power BI dashboards.

I have built a mock up dashboard with 2 tiles:

1. Amount per year
2. A sensitivity analysis (tile with the value 0,5)

The first item can be built in power BI directly from the data. No problem here.

The second tile require some data modelling. It can be done in power BI using add new measure. Column M is the end result and is summarized in Power BI to 0,5 (shown for illustration purposes).

(The modelling contains two "IF" formula and two simple "+" and "*" formulas. )

I’m in doubt if this is the correct approach and if good practice is to do data modelling in excel/power pivot.  I actually have a preference for doing the modeling in excel since it is calculation “on top” of calculation to get to the correct data.

So here are my questions:

Should the last item be calculated in Power BI or first in Excel and why?

When does data need to be calculated first in Excel instead of directly in power BI using a “new measure”?

Is it “OK practice” to have the same underlying data for tile 1,2 above where one is put directly into Power BI where the second tile is routed first to excel for some additional calculations?

[V-lianl-msft]

Hi @Anonymous,

Almost no difference,It's just that some DAX functions can't be used in power bi.

When you import power pivot into the power bi desktop, the refresh will still synchronize with power pivot.

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickl

[Anonymous]

Ok @V-lianl-msft , so I assume you mean that my example should be done directly in power BI. Could you give an example where you would first do some calculations in excel? 

[V-lianl-msft]

Hi @Anonymous ,

Power Pivot is a calculation engine for pivot tables.

We can use Power Pivot in either Excel or Power BI.

If you're doing it in power bi you just need connect excel, if you're doing it in excel you need to import the power pivot model.

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Liang,

Ok, i understand that. What im unsure of is when its needed to do calculations in excel first. I have red about it, it should be when complicated excel modelling is needed, but i would like to get a better understanding of that. Could you give an example?

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Although PowerPivot has some data storage capability, as a built-in plug-in of Excel, its performance cannot match that of Power BI

Desktop and SQL Server.

Here is a blog about the performance comparison of PQ in Excel and power Bi desktop:https://www.thebiccountant.com/2017/05/14/performance-considerations-for-bom-solution-in-excel-and-powerbi/ 

(Note: This link contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.