Accurate Total of Measure when using a filter and if statement

[spsullivan]

I have an issue that I have been unable to solve.  In the attachment, I have a matrix visual filtered by Fund.  As you see, the total for 'A Real Value' is not the total of the line items.  I understand why (mostly) but cannot get the forumla to accurately  reflect the line items above.  The line items ('A RealValue') is an  if statement on the value in the AFP column.  If the AFP value <> 0, then I wan the value shown in A RealValue to be AFP - Commitments.  If it is not, then show 0.  Finally, I want the total of the column to match  the  sum of the  items in that column ('A RealValue).  Below, is the formula I have for the measure:

A RealValue =
if(HASONEVALUE(qryAllFY_0[Fund]),
    if(SUM(qryAllFY_0[AFP])<>0,
        sum(qryAllFY_0[AFP])-sum(qryAllFY_0[Commitment]),0),
            sumx(qryAllFY_0,[AFP] - [Commitment])
)

[v-yuta-msft · Accepted solution]

Hi spsullivan,

The issue is because the grand total just calculate the current row context value, not the sum of calculated values above. For details, you can refer to this thread: https://social.technet.microsoft.com/Forums/en-US/a2d98de7-42bf-473f-8aef-2b676c27b85f/measure-grand....

In addtion, you can modify your calculate column like pattern below and check if it can meet your requirement:

A RealValue =
SUMX (
    VALUES ( qryAllFY_0[Fund] ),
    IF (
        SUM ( qryAllFY_0[AFP] ) <> 0,
        SUM ( qryAllFY_0[AFP] ) - SUM ( qryAllFY_0[Commitment] ),
        0
    )
)

Regards,

Jimmy Tao