Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
)
Solved! Go to 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |