Forum Discussion
Incorrect matrix row total
A matrix report shows expected values and correct column totals. The displayed row totals are not equal to the sum of the row values. A snip of the report:
desktop matrix report
Surely 8+21+9+32 !== 59; 259+454+113+327 !== 981.
The equivalent PivotTable in Excel has identical values but with the correct row and grand totals. How can this be corrected?
Do you have a visual level filter on for site types with a total measure that contains an ALL() function? That would explain why you have column totals correct but row totals incorrect. Are you able to share the PBIX?
The report will show the correct row totals if the SQL statement that appears above is used at Get Data. This seems to reduce the value of PBI Desktop if identical results are not possible without pre-selecting data.
13 Replies
I hope this helps somone. After much fustration with the default matrix, I've found that The Acterys Matrix Light visual, available from the markplace, appears to sum rows correctly.
Microsoft, please can this be fixed in the default matrix, as it is causing some real problems for many people.
Thanks.
Do you have a visual level filter on for site types with a total measure that contains an ALL() function? That would explain why you have column totals correct but row totals incorrect. Are you able to share the PBIX?
Yes, there is a visual filter for Site (a relabel of a MySQL table named center). The data are not sensitive. How best to share? It can presumably be found on Dropbox here.
Edit 2: If including all causes an incorrect total I would expect the total would be greater than the sum of the displayed values. In this case, the displayed totals are less than the sum of the values.
FWIW, I'm on my first day of working with PBI Desktop. I'm looking at it to see if it would be at all appropriate for a local nonprofit that has little in-house IT capability.
Edit 1:
The pivot table to which the pbix is being compared has also been uploaded to Dropbox. The SQL statement used in Power Query was:
SELECT DISTINCT c.household_id, hs.housing as Housing, r.center as Site
FROM contact c
JOIN household h ON c.household_id = h.id
JOIN center r ON r.id = c.center_id
JOIN housing hs ON hs.id = h.housing_id
WHERE fy(c.contact_date) = 2016;where fy() is a MySQL UDF.
The report will show the correct row totals if the SQL statement that appears above is used at Get Data. This seems to reduce the value of PBI Desktop if identical results are not possible without pre-selecting data.
I had an ALL() in one of my measures. Once I cleared that, the rows were corrrect. Thank you!
Please vote for this idea so it can get more traction to be resolved:
Fouind a useful workaround with one extra measure here:
(348) Power BI Shows Incorrect Measure Total? How to fix it? - YouTube
