Columns subtotal does not display correctly

[niculeica]

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.

[Anonymous]

Hi,@Wilson_ ,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 Learn
HASONEVALUE function (DAX) - DAX | Microsoft Learn
 

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]

Hi, @Anonymous, @Wilson_ ,

Sorry, should've been more explicit here😁 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.

_ActualRevenue =

VAR ProjectName =

SUMMARIZE('Mavenlink-Projects&Tasks','Mavenlink-Projects&Tasks'[Project: Name],"Actual ARR",[_ActualRevenue raw])

RETURN

IF(IF(HASONEVALUE('Mavenlink-Projects&Tasks'[Project: Name]),[_ActualRevenue raw],SUMX(ProjectName,[Actual ARR]))=0,BLANK(),

    IF(HASONEVALUE('Mavenlink-Projects&Tasks'[Project: Name]),[_ActualRevenue raw],SUMX(ProjectName,[Actual ARR])))

Are you suggesting, @Anonymous , I should do the same in terms of Dates hierarchy? Like below?

IF(ISINSCOPE(Calendar[Date]),_ActualRevenue,SUMMARIZE(Calculations,Calculations[Date],"ActRev",SUMX(Calculations,[_ActualRevenue])))

[Anonymous]

Hi,@niculeica .Thank you for your reply.
It looks like you're starting to try out my suggestions, and in fact, using the ISINSCOPE function to force changes to the data in the matrix is a very common way to do this. I notice that your measure is quite complex, so you need to be aware of the real computational context in which the total columns are displayed and make sure that the filters are correct. If you have external slicers and field filtering in the filter area, you will also need to take that into account. I hope you realize your needs soon.

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.

[Wilson_]

niculeica,

Can you please share your pbix (or a mock pbix, if data is sensitive)? If you don't know how, please check the pinned thread in the forum.

I have a feeling your measure doesn't need to be nearly this complicated but I can't confirm without more information and context. Totally get that you're hiding information you don't think is relevant (likely for security reasons) but it makes it harder to give a good answer in this case.

[niculeica]

Hi, @Wilson_ ,

Please find the mock file through the link below.

https://www.dropbox.com/t/gsCXUZ9M4KYOMhSp

[Anonymous]

Hi,@niculeica.

Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.
Unfortunately, due to my work environment, I don't have access to the pbix link you provided.

How to access your data quickly:

https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommu...

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]

Hi, @Anonymous ,

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

[Wilson_]

niculeica,

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). 🙂