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
gracechong
Helper I
Helper I

Combine ABS and Page Filter in a measure but Grand Total is incorrect

Hello, 

 

This is my first post ever cause I have been cracking my head over the solution and I'm not sure if I understand the other solutions that I read correctly as the results are not what I had in mind. 

 

Capture1.PNG

 

The table visualisation above shows two different columns with the second column (Difference) showing correct grand total @ 6,528K whereas the first column ($Impact difference) is showing the same total which is wrong; as it should be showing @ 9,118K. The ($Impact difference) column is a measure based on the (Difference) Column where I wanted to discard all the negatives and just change them into absolute values. 

 

Below is the formula I use for the measure. Can you spot what is wrong with the formula that causes the incorrect grand total for the measure? As there is a page filter in the visualisation, I had to filter the "Project_ID","Department" and "Business Unit" field.

 

$ Impact Difference = abs(calculate(sum('table1'[Difference]),allexcept('table1','table1'[Project_ID],table1[Department],calcvsposted[Business Unit])))

 

Your advice is much appreciated!

 

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

Hi @gracechong

Try this measure

Measure =
IF (
    HASONEVALUE ( Table1[Department] ),
    [$ Impact Difference],
    SUMX ( ALL ( Table1 ), [$ Impact Difference] )
)

13.png

 

Best Regard

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @gracechong

Try this measure

Measure =
IF (
    HASONEVALUE ( Table1[Department] ),
    [$ Impact Difference],
    SUMX ( ALL ( Table1 ), [$ Impact Difference] )
)

13.png

 

Best Regard

Maggie

Stachu
Community Champion
Community Champion

I think the issue is you want the sum of ABS values, and formula first sums then applies ABS
try this one

$ Impact Difference =
CALCULATE (
    ABS ( SUM ( 'table1'[Difference] ) ),
    ALLEXCEPT (
        'table1',
        'table1'[Project_ID],
        table1[Department],
        calcvsposted[Business Unit]
    )
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hello,

 

I am still getting the same incorrect grand total with the updated formula. So I tried reading around to find solutions and I somehow get the feeling I should incorporate the "SUMX" formula with this updated formula so that I can get the correct grand total but I am not too sure on how to apply them in my context. Would you be able to help?

 

Grace

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.