The result is shown below: Remove Duplicates Setup table In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns. My problem is that the column total is 52 while the sum should be equal to 358 !! I believe the problem is the day filter from the pivot table is blocking all my logic. The grand totalt is wrong. For example, order 101 has two lines (rows), and order 102 has 3 lines. This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable. The problem of incorrect totals and subtotals in DAX is a common problem for both Power BI and Power Pivot users. I can NOT get this to work. ... And then your total maybe =SUM( Aggr( Count(DISTINCT Survey), Question, Answer)) ... wrong total. Refresh the Pivot Tables Naturally, as there are 2 Pivot Tables involved in this solution, both have to be refreshed after any data has been added or changed in the source table. Right-click one of the values in the Person field. In this post, weR… In the Value Field Settings dialog, click Summarize Values By tab, and then scroll to click Distinct Count option, see screenshot: 5. Totals on Pivot table are incorrect The pivot table has 1 row, 2 columns and 1 measure. Below are the steps to get a distinct count value in the Pivot Table: Select any cell in the dataset. This will open a window where we can choose Distinct Count as a calculation type. The nuance here is that Grand Totals are calculated on a field basis. Pivot tables also have a Count Numbers function, which is like the worksheet COUNT function. I have then added another column (E) to count duplicate rows. Celia. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once). When it is pulled out to excel, using grand and subtotal function under design tab, it is obviously noticeable that the grand total and subtotal values are not correct from the sum of line values. Prior to Excel 2013, this capability was not built-in to the PivotTable feature. For Excel … Let’s try this with a traditional PT first. We can count values in a PivotTable by using the value field settings. Cause This problem occurs when you use a calculated field (a field that is based on other fields) in a PivotTable, and the calculated field is defined by performing a higher order arithmetic operation, such as exponentiation, multiplication, or division on other fields in the PivotTable. There are three unique distinct rows (Grand Total) Pivot table: Count duplicate records (rows) The table I am working with in this example is in cell range B2:D12. Yet, when I refresh the existing pivot table, it includes a distinct count of 3. The row is School Building, the 2 columns are School Year and Therapy Type. If there are 5 customers who bought product A and of 3 customers bought product B (of the 3 who bought product B, 2 also bought product A), the total number of customers is not 5+3=8. I can get the total for the whole table, I can get mtd, ytd, and the rest. Cause This problem occurs when you use a calculated field (a field that is based on other fields) in a PivotTable, and the calculated field is defined by performing a higher order arithmetic operation, such as exponentiation, multiplication, or division on other fields in the PivotTable. That initially gives us numbers we don't want in our summary report. If you create Count, CountA and Sum totals in the source data, only the Sum totals show errors. In a PivotTable, Microsoft Excel may calculate an incorrect grand total for a calculated field. Using total with count, set expression and date. Hey My grand total is wrong when using distinct count, if the visualisation is a matrix, table or any. The Person field changes, and instead of showing the total count of transactions, it shows a distinct count of salespeople names. I have tried some DAX formulas suggested in other Threads for summarize values of the distinct count column, but I always get the same result: the total sum is either the one in the column with Distinct count or the one summing the counts. The table is a pivot table with one dimension for the Question and another one with the answer. When you build your pivot table this time, you are going to drag Deal ID to the Sum of Values area. I'd read this the post, but my problem seem to be slightly different. Hi @v-danhe-msft, thanks for the suggestion. To fix this, we want to right-click on the Sum of Deal ID column header and select Value Field Settings. To get distinct count in the pivot table, These instructions work only in Excel 2016, Office 365 and Excel 2013. Here 1606 is not the sum of all the above Stores instead it is taking as DISTINCT COUNT in Grand Total also. non Response is COUNT(DISTINCT Stores) - SUM (Response). Click Value Field Settings. By definition when doing a distinct count over such a model then the grand total should not always be the sum of the rows above. To add the running total column, simply insert the Item field into the Values area again, and set the field to display the Distinct count. I have tried the following workaround that I found on stackoverflow: For each value, Sum or Count is the default summary function, when added to the pivot table. Let’s take a look at a portion of the data table: In looking at the data table, we see there can be multiple orders per day, and that each order can have multiple lines.