Forum Discussion

niculeica's avatar
niculeica
Helper I
2 years ago

Columns subtotal does not display correctly

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/...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    niculeica
    Helper I
    2 years ago

    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])))
    • Wilson_'s avatar
      Wilson_
      Memorable Member
      2 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.