Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
geoB
Regular Visitor

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 reportdesktop 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?

2 ACCEPTED SOLUTIONS
samdthompson
Memorable Member
Memorable Member

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?

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

geoB
Regular Visitor

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.

 

 

View solution in original post

13 REPLIES 13
HenryWatts
Regular Visitor

Fouind a useful workaround with one extra measure here:
(348) Power BI Shows Incorrect Measure Total? How to fix it? - YouTube

vpatel55
Kudo Commander
Kudo Commander

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.

Anonymous
Not applicable

Good Day,

 

I know this post is quite old but I am facing an issue where the the total of a column is not the same when I export the data to excel. Is there an explaination for this or work around. 

 

Thanks in advance.

 

Kind Regards

Bash

kliberty
Frequent Visitor

Please vote for this idea so it can get more traction to be resolved:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17298271-matrix-totals-only-as-su...

samdthompson
Memorable Member
Memorable Member

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?

// if this is a solution please mark as such. Kudos always appreciated.

I had an ALL() in one of my measures. Once I cleared that, the rows were corrrect. Thank you!

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.

geoB
Regular Visitor

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.

 

 

Hi. the matrix is showing the data correctly. For instance Household ID 4319 appears in Car/Tent in Incline Village, Kings Beach, Tahoe City and Truckee. You have set the value to a distinct count and so it is correctly counting the housing ID as 1 for each of the housing site (distinct count of housing ID). Where you are coming into trouble is that you have a total which is the distinct count of housing ID's which in the case of 4319 is 1. You are expecting it to sum to 4 but its only 1. This is infact correct just not what you wanted. The simplest, but not the most elegant solution would be to make a calculated column which is a concatenation of housing id and housing site, then do a distinct count of that.

// if this is a solution please mark as such. Kudos always appreciated.

I'm glad to know the explanation.  My efforts to create the expressions you suggest have not yet been successful.  And I've taught Excel & Access at the local community college in the not too distant past. While I don't believe the organization I'm researching this for could figure all this out I'd very much like to see how it's done.  Can you suggest some documentation that would enlighten me?

geoB
Regular Visitor

The more I think about this the more absurd the situation appears.  I cannot imagine a scenario where it is appropriate to apply an algorithm other than summing the row values.  Under what circumstance is displaying a value other than the sum of row values appropriate?  Why should this not be considered a bug?

Hi @geoB i ran into the same problem, and completely agree with your thoughts there can't be a situation when this unique values row sum makes sense.

 

By any chance did you figure a way around to display the right sum values?

Thanks for duplicating the result - suggests it wasn't my bad design! Given that I was effectively told the result was by design and that I could never find a work-around I gave up with BI. Off to smaller and better things.

 

Good luck.

 

g

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.