PowerBI: Getting Pivot table level of flexibility with calculated fields

Question

The great (for me at least) thing about having reports built in Excel with Pivot Tables and Charts is that I can have a calculated field that reacts dynamically to whatever is displayed (sliced) at a given time.

From my pretty short experience with PowerBI this does not seem to be possible. This discussion appears to be dealing with the same/similar issue and brings no good news. In general terms, the purpose of this question is to understand if this is still the case or whether in the year since the aforementioned discussion took place something changed.

In more detailed terms I currently have some reports in Excel relying heavily on pivot tables and charts. The data source is stored in Access. In the report, I need to display some weighted averages. Right now the first step of the calculation (multiplication of values with weights) is done in Access while in Excel a pivot table calculated field takes care of creating the dividend (sum of products) and divisor (sum of weights). Due to the nature of pivot tables the results will always be correct no matter how much the end users go wild in drilling down with the slicers or vice-versa if the data is aggregated at a higher level than the one found in the raw source.

I haven't found a way to achieve the same degree of flexibility with PowerBi. Is there any general solution?

--

EDIT in reply to Alexis Let me give you a more tangible example, this is a view I have in Access that feeds a report which has the purpose of monitoring the average handle time (execution speed) of the employees.

Day        |      Month |     Agent |  Team | Category| CategoryDetail| Volume | Time (s) | CF_Product (Volume*Time)
05/01/2018 | 01/01/2018 | JohnSmith | TeamA | Tier1   | Mail          | 15     | 350      | 5250
03/02/2018 | 01/02/2018 | SamAllen  | TeamB | Tier1   | Phone         | 25     | 60       | 1500

The purpose of CF_Product is to prepare the weighed average calculation. Time (s) is the value and Volume is the weight. Using Excel my next steps would be:

1. Create a pivot table using this view as data source;
2. Create a calculated field as follows CFE_WeightedAverage = CF_Product/Volume
3. Plot this information on a line/combo chart with the Day field on the X-axis.
4. Put slicers for all descriptive fields (team, agent, category..) so that the users can drill-down / -up as they please.

In PowerBI:

1. I load the view in the model
2. Select a combo/line chart
3. Create a calculated column, precisely as I would in Excel CFE_WeightedAverage = CF_Product/Volume
4. Drag this field in the line section of the chart,

however, the data displayed is "wrong". I guess this is because a pivot table in excel will first aggregate and then perform the calculation on whatever aggregate values are cached at a given time, whereas in power bi the calculation will be performed on each row of data and then summed together and I don't know how to work around this problem.

Hope this clarifies better my case.

Answer 1

In Power BI, there are measures and calculated columns. If you want your calculations to be responsive to slicers or cross-filtering, then you need to be using measures instead of calculated columns as the latter is computed before anything happens on your report page.

There are plenty of resources describing the differences between these two that you kind find with your search engine of choice. Here are a couple of sources:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/ https://powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/

Answer 2

Using the DAX formula language, you can do incredibly powerful calculations in either PowerBI OR a PowerPivot PivotTable in Excel that far surpass anything you could do in 'traditional' PivotTables in Excel. The only difference between PowerBI and Excel in this regard is the UI: PivotTables in Excel have much better 'drag and drop' options than say a Matrix in PowerBI.

The discussion you link to is about drill-down, and doesn't seem to be relevant to your question about the dynamic display of information at all.

To answer your question about whether anything has changed, one could arguably say "No: PowerBI and DAX-pimped PivotTables in Excel still you greater control over what displays in a particular visual or PivotTable compared to traditional PivotTables".