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
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])))
Anonymous's avatar
Anonymous
Not applicable
2 years 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.