Missing detail lines in Table Matrix

[apbiuser]

     I am using the June 2018 64-bit version 2.59.5135.601 of PBI Desktop.

     I have a budget detail file that is loaded via a PBI Query, using SQL Server as a source, to a table named SalesBudget.  I have verified that all of the records from the SQL Server database are being loaded into the PBI table SalesBudget.  The hash totals and record counts have been verified.

     I am using the Table visualization to view the SalesBudget data.  No filters, nothing is applied just pull the data straight into the visualization.  The Totals in the Table visualization do NOT match the hash totals from the source table.  The Table visualization is missing 12 out of 514 source table records.  Using additional Slicer visualizations I am able to find examples of the misssing entries.  In the visualization I SUM 3 columns and all 3 totals are off by millions.  I don't understand how a straight out load to the visualization could not add up.

     I use the SUMMARIZECOLUMNS function to aggregate the SalesBudget table into a result set called BudgetSummary.  I load the result set into a Table visualization.  The Totals in the Table visualization DO match the hash totals from the SQL Server source down to the penny.  

     The remove the possibility of any other oddities I put the two visualizations on the same Page within the report.

We cannot understand a number of things:

   1) Why does the detail table visualization not match the query result set, coming up short?

   2) How can you summarize the detail table and the summary be correct?

We have burned hours trying figure out what is causing the anomally.  Any assistance would be greatly appreciated. 

[Greg_Deckler]

Really tough to say without the data or the PBIX file to mess around with. One possibility is that for some reason in the table visualization it is dropping some rows because of lack of information. If a value is null or blank it tends not to get displayed. Check your FILTERS area. Just really difficult to answer this one.

[apbiuser]

More info - I tested the filter theory by putting the Visualizations on the same page in the report.  It did not make any difference, the summary visualization total (CORRECT) was still greater that the detail visualization total (INCORRECT). 

I attempted to add a graphic to this post using the Photos tool but it will not accept the BMP that I have.

Thank you for reviewing my original issue.

[apbiuser · Accepted solution]

     I have found the issue.  I have report level filters.  The report level filter is causing the omission of detail lines from the detail visualization table SalesBudget which is attached to the star schema.  There is not an omission of data in the Sales Budget Data Summary SUMMARIZECOLUMNS derived table visualization since it is not attached to the star schema even though it summarizes over a table in the star schema. 

     I would have expected that the result set totals would have been the same.  Using the SUMMARIZECOLUMNS could prove to be problematic when there are filters applied and aggregation is needed.         

[v-yuezhe-msft]

@apbiuser,

If you have solved the issue, you can accept your reply as answer to close this thread.

Regards,
Lydia

[apbiuser]

Yes this is a tough one!

When I dive into the detail records I am able to find an example where a customer has 6 budget records for the year.  One record each for month 2 Feb, 4 Apr, 6 June, 8 Aug, 10 Oct, and 12 Dec.  The data appears the same in the data previewer.  One of the rows that is being left out of the Table visualization is for month 6 June.  The base SalesBudget table is standalone, no relationships.  I attempted to post a screen capture of the data records but the tool attached to this board does not allow me to navigate to the saved bmp.