Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |