Forum Discussion
Incorrect matrix row total
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.
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.