Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Solved! Go to Solution.
Hi @gracechong
Try this measure
Measure = IF ( HASONEVALUE ( Table1[Department] ), [$ Impact Difference], SUMX ( ALL ( Table1 ), [$ Impact Difference] ) )
Best Regard
Maggie
Hi @gracechong
Try this measure
Measure = IF ( HASONEVALUE ( Table1[Department] ), [$ Impact Difference], SUMX ( ALL ( Table1 ), [$ Impact Difference] ) )
Best Regard
Maggie
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] ) )
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |