Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spsullivan
New Member

Accurate Total of Measure when using a filter and if statement

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])
)


Sample.png

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.