Columns subtotal does not display correctly

Question

Hi! Any idea why Columns subtotal does not display correctly for the below matrix snip shot? The idea is Total (sub)columns would sum up all the applicable months before, per each respective revenue/cost type, however, it seems like it's only adding up the values of last month.&nbsp;&nbsp;

niculeica · Answer

Hi,&nbsp;Wilson_&nbsp;,&nbsp;Please find the mock file through the link below.&nbsp;https://www.dropbox.com/t/gsCXUZ9M4KYOMhSp

niculeica · Answer

Hi,&nbsp;Anonymous&nbsp;,&nbsp;Nope, haven't found the solution yet. I'll give it some more attempts today. Thanks!

wilson_ · Answer

niculeica,
&nbsp;
Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. You have not shared any information like your calculations, data model (tables and relationships). 🙂

anonymous · Answer

Hi,Wilson_&nbsp;,thanks for your concern about this issue.
Your answer is excellent!And I would like to share some additional solutions below.Hello,niculeica .I am glad to help you.
Based on your description, you mentioned that in your matrix, the row subtotals that come with the matrix are not calculated correctly (Actual Revenue and Actual Cost in the chart).Here is my understanding, you may want the Actual Revenue value to be the sum of the Actual Revenue values in Novemver 2024 and December 2024:480.804+475.790=956.594, but it is not displaying the aggregated values as expected.
In fact I would recommend that you use the ISINSCOPE function for hierarchical judgment, I notice that your matrix data is filtered by the top level date column. You can try using IF judgment:
IF(isincpoe('Table'[Dates]),measure1,sum of measure1)
That is, you need to manually aggregate the corresponding data for each column by manually modifying the value of the total column through the judgment matrix hierarchy. Instead of using the default system auto-aggregation approach.I discussed your issue with other members within the team. The conclusion is that this is a normal situation in the matrix, because the total column in power bi is calculated without the filtering environment of the previous two columns in your matrix, the row context has changed, and at this time the row total is calculated only according to the value of the MEASURES, and if there are multiple filtering conditions in your matrix or the writeup of the MEASURES is more complex (defined with If there are multiple filters in your matrix or if the measure is written in a complex way (with multiple variables defined), it is possible that the answer to the total row subtotal will not appear as expected.
Therefore, I suggest you try to modify your measure by adding the ISINSCOPE() function to determine the level of filtering without changing the normal output, and artificially defining the final result of the row subtotals instead of using the system's default calculation, which will bypass the error you are experiencing.Use ISINSCOPE or HASONEVALUE to judge the matrix hierarchy to artificially modify the display results, especially the final result of the total column, which is very commonly used in matrices and tables, the following is the relevant dax function, I hope it will help you.URL:ISINSCOPE function (DAX) - DAX | Microsoft LearnHASONEVALUE function (DAX) - DAX | Microsoft Learn&nbsp;
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

niculeica · Answer

Hi,&nbsp;Anonymous,&nbsp;Wilson_&nbsp;,&nbsp;Sorry, should've been more explicit here😁&nbsp;There's a Calendar table in relationship to the matrix, indeed. The months are pulled from it. The formula for Actual Revenue, for example, is below.&nbsp;_ActualRevenue =VAR ProjectName = SUMMARIZE('Mavenlink-Projects&amp;Tasks','Mavenlink-Projects&amp;Tasks'[Project: Name],"Actual ARR",[_ActualRevenue raw])RETURNIF(IF(HASONEVALUE('Mavenlink-Projects&amp;Tasks'[Project: Name]),[_ActualRevenue raw],SUMX(ProjectName,[Actual ARR]))=0,BLANK(),&nbsp; &nbsp; IF(HASONEVALUE('Mavenlink-Projects&amp;Tasks'[Project: Name]),[_ActualRevenue raw],SUMX(ProjectName,[Actual ARR])))&nbsp;Are you suggesting,&nbsp;Anonymous&nbsp;, I should do the same in terms of Dates hierarchy? Like below?&nbsp;IF(ISINSCOPE(Calendar[Date]),_ActualRevenue,SUMMARIZE(Calculations,Calculations[Date],"ActRev",SUMX(Calculations,[_ActualRevenue])))

Forum Discussion

Related content

Customize subtotal in matrix

Subtotal isn't adding up correctly

Matrix column Subtotal

Column Subtotals Errors

Hierarchy Not displaying subtotal correctly