Both Tableau and Power BI offer powerful calculation capabilities. Tableau emphasizes table calculations and LOD (Level of Details) expressions for interactive visualizations, while Power BI relies on DAX measures and calculated columns for dynamic, context-aware calculations in reports and dashboards. First let’s focus on what each calculation does in these two Viz Tools:
1. Table Calculations: Tableau offers a variety of table calculations that operate on the data displayed in a visualization. Examples include running totals, percentage of total, moving averages, and rank functions. These calculations work with the data already in the view and allow you to create dynamic, context-aware visualizations.
2. Level of Detail (LOD) Expressions: LOD expressions in Tableau let you create calculations that ignore the visualization’s level of detail. You can perform calculations at a different granularity than what’s displayed in the view, making it useful for complex aggregations and comparisons.
Now let’s have a look at the calculation in Power BI:
1. DAX Measures: In Power BI, you primarily use Data Analysis Expressions (DAX) to create measures. DAX measures are dynamic calculations that respond to filter context and user interactions. You can use DAX to perform various calculations, including aggregations, time intelligence, and complex calculations.
2. Calculated Columns: Power BI also allows you to create calculated columns using DAX. These are row-level calculation that adds columns to your table and data model, computed during data refresh.
With all these calculation functions, here we will explore how to calculate the percentage of total using Table Calc & LOD in Tableau and DAX Measure in Power BI. Here we will be using the Sample-Superstore Data from Tableau Sample Dataset to calculate the percentage of total sales for each sub-category in the total Sales.
1. Tableau Table Calc
Tableau table calculations do not generate additional database queries, and it leverage the data that is already in your Tableau workbook, allowing for interactive and responsive visualizations without the need for additional database queries. This means it can potentially allow dashboard interactions to run more smoothly. Here’s how we can achieve the goal by tableau table calc:
Drag Sub-Category to rows and Sales to Text. Then click on the little triangle on the right of the SUM(Sales) tab, select “Add Table Calculation” from the drop down menu.
In the pop out window, select “Percent of Total’ from the drop down menu in the calculation type, and computing using “Table Down” (here is the sub-category along the Table Down). And the final result can be seen from the view below, where the percentage of sales of each sub-category adds up to 100%.
2. Tableau LOD
The second way to achieve this in Tableau is using LOD calculation. LOD expressions can generate database queries, especially when they are used in conjunction with live connections to data sources. While it also means that the calculation can be used across different visuals or to form new calculations. Here’s how to achieve the goal with LOD calculation:
In Tableau, the level of granularity in a visual is primarily determined by the fields you add to the visualization’s Rows and Columns shelves as well as marks pane. So as you drag [Sub-category] into the visual, the SUM(SALES) function will automatically aggregates to the [Sub-Category] level. In order to calculate the percentage of total sales for each sub-category, we need to use a LOD calculation to tell Tableau to return the total sales for the whole dataset. The following calculation is telling Tableau to return a constant value representing the total sum of sales across the entire dataset, regardless of any dimensions or filters applied to your visualization.
Then you can calculate the percentage of total by the following calculation, and change the number format to a percentage:
Then when you drag the [% of Total LOD] calculation to the Text pane, it will show the same result.
3. Power BI DAX Measures
Now let’s build the table with Power BI. After loading the dataset to Power BI, first we will create a table that stores all the measures that we will be using. To achieve that, press “Enter Data” from the “Home’ tab. In the pop-up window, type in “Measure Tables” as the table name.
Then the table “Measure Table” will show up in the Data pane on the right. Select the table and then Click “New Measure” under “Table Tools” Pane.
There are three measures that we will be creating: [Total Sales], [All Sales], and [% of Total Sales].
• [Total Sales]: It aggregates the sum of Sales, and when you put the measure in the visual, it will apply a filter context for the Dimensions that you drag in view, which is Sub-Categories in this case. The Formula is as follows:
• [All Sales]: It returns the Total Sales for the whole dataset, ignoring all the filters that is applied. The DAX Function is as followed.
It uses the CALCULATE function along with the ALL function to modify the filter context and calculate the total sales while ignoring any filters applied to the “Orders” table. Here’s a step-by-step explanation:
– [Total Sales]: This is the previous measure that computes the total sales amount based on the current filter context.
– ALL(Orders): The ALL function is used to remove filters or context applied to the “Orders” table. It’s clearing all filters, [Sub-Category] in this case, on the “Orders” table, effectively considering all rows in the “Orders” table for the calculation.
– CALCULATE([Total Sales], …): The CALCULATE function is used to modify the filter context for the calculation of the “[Total Sales]” measure. It takes two arguments: the measure to calculate and the filter modification (in this case, ALL(Orders)).
Putting it all together, this DAX formula calculates “All Sales” by taking the “[Total Sales]” measure and recalculating it, but this time it considers all rows in the “Orders” table, regardless of any filters or slicers applied in your Power BI report.
• [% of Total Sales]: It returns the percentage of total sales for each sub-category by dividing the [Total Sales] Measure by [All Sales] Measure.
The DIVIDE function is used to divide the [Total Sales] measure by the [All Sales] measure to calculate the percentage. The third argument, “No Sales,” is a text string that serves as the result if the denominator (i.e., [All Sales]) is zero. In this case, if there are no sales (denominator is zero), it will display “No Sales” as the result. Change the data format to percentage after the calculation is done.
Now with these measures done, we can build a Matrix Table by clicking the Matrix Button, add [Sub-Category] to Rows, add [% of Total Sales] to Values. The results shown below are the same as the previous two tables.
In this blog, we have explored calculation of percentage of total in Tableau Table Calc, Tableau LOD, and Power BI DAX. Witi diverse ways to achieve the goal, it is important to understand how each calculation function works and how it is carried out, to choose the one that suits the purpose best, and to ensure the intergrity of the data presented.