Matrix Column Total - Summing each Row Value with a measure value

[mngma0102]

Hello,

I have a matrix showing forecasted vs purchased data. The Forecasted data is updated for each Forecast # however the Purchased data will be the same for each Forecast # since the time-range is the same and it is from the past.

The problem I am running into, is that I would like the for the Purchased Column to SUM each row value so the Grand Total  equals 463,646.  I would like to do this so I can get an acccurate % for the Purchased/Forecast column.

The Purchased column is using a imported measure which I am unable to change. 

Thank you in advance! 

[Anonymous · Accepted solution]

Hi,@mngma0102 I am glad to help you.
Here are my tests:
Available observations
When the value of measure is put into the matrix, the aggregated value of the total row/column that comes with the matrix is affected by the function currently used for measure
When MAX is used, the final aggregated value of total is the maximum value from the measure [Purchased] calculation 93459

When the SUM function is used to modify the measure, the result of the total aggregated value is the total value of the measure sum 231823

So if you want to change only the value of the measure in the total column, while keeping the original measure calculation logic, you need to use the if function to determine the current matrix row hierarchy: if the rows are filtered by fields at different levels, different calculation logic is performed.
You can use the HASONEVALUE function or the isinscope function, their role in the matrix is similar.
measure:

M_Purchased = 
SWITCH(TRUE(),
HASONEVALUE(Tabelle1[C_Forecast #]),SUM(Tabelle1[C_Purchased]),
HASONEVALUE(Tabelle1[YearMonth]),MAX(Tabelle1[C_Purchased]),
SUM('Tabelle1'[C_Purchased])
)

M_Pur/For = DIVIDE('Tabelle1'[M_Purchased],[Forecasted],0) 

Here is my test data:

It should be noted that the above is my test data based on the screenshots you provided, you need to create a new measure based on the data in your actual environment, not directly use the measure I provided, because everyone's computing environment is inconsistent, and the measure will be affected by different computing environments to display different values.
Because everyone's computing environment is not the same, and the measure will be affected by different computing environment to display different values.
I hope you can write a measure that meets your needs soon.
If you can provide a .pbix file that does not contain sensitive data and share it on the forum via OneDirve, it will help solve your problem.

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.