Forum Discussion
Why does my matrix table not total correctly
Hi as you may see my standard cost and variance row isn't totalling correctly. Standard should be total of 9,392. I believe this is because of the background data. If I select the same sub assembly in the data sheet it brings up the same sum. The visual table is correct so how do I get it to sum that instead to show 9,392?
Matrix totals in Power BI often recalculate instead of summing row values. To fix this, use SUMX to force row-wise aggregation:
Total Standard Cost =
SUMX(VALUES('YourTable'[Serial Number]), [Standard Cost])
7 Replies
What is the formula behind Standard Cost? Is Standard Cost a Measure or a Column?
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
This is how my matrix table is currently set up.
Standard cost is a column. It comes from the standard cost sheet. Sub assembly also comes from this table.Engine Spec Sub Assembly Std Cost Notes/Info Financial Year 300 1 Air inlet cassing assembly 1332 24 300 2 Air inlet cassing assembly 1332 24 300 3 Air inlet cassing assembly 1349 25 300 4 Air inlet cassing assembly 1349 25
Serial number comes from the combined dispatches sheet.Engine Spec Serial number Project definition Standard cost per engine Fiancial year 300 1 X048 1600 50000 24 300 2 X051 1602 50000 24 300 3 X055 1650 70000 25 300 4 X060 1617 65000 25
The actual cost comes from the project data sheet.Project definition Actual cost Sub Assembly Financial Year 1600 500 Air inlet casing assembly 24 1600 500 Air inlet casing assembly 24 1600
10 Air inlet casing assembly 24 1600 64 Air inlet casing assembly 24
Variance, RAG status, and Data bar are visual calculations.The matrix should look like so;
Sub assembly serial number standard cost actual cost air inlet casing X048 1332 1174 X049 1332 1000 X051 1332 850 X055 1349 100 X057 1349 1500 X060 1349 1400 X054 1349 1000 TOTAL 9392 7024 If Standard Cost is a column, you must have an aggregation setup, what is that? It might be that the automatic aggregation done on columns when you aggregate the in the Values section of Visuals is not the one you want. Please check it
I suggest you, anyway, to create a measure
Standard Cost Measure = SUM ( Table[Standard Cost] )
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Matrix totals in Power BI often recalculate instead of summing row values. To fix this, use SUMX to force row-wise aggregation:
Total Standard Cost =
SUMX(VALUES('YourTable'[Serial Number]), [Standard Cost])Hi Anonymous ,
Have you tried following FBergamaschi , suggested approach of creating a measure. If not, please give it a try and take the necessary action. If you need any additional details, please let us know.Thank You.
Hi Anonymous ,
I wanted to check in regarding your issue. Has it been resolved, or do you need any further information. Let me know if you’d like more details.
Thanks.
Hi Anonymous ,
Just following up has your issue been resolved, or are you still having any difficulties? If you need more information or help, please let us know.
Thanks.
