Forum Discussion
Why does my matrix table not total correctly
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])
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