Forum Discussion
Solved
Balance sheet modeling
Hi everyone, I have an issue with data modeling. I have a table of accounting movements and another table containing the reclassified balance sheet schema. The link between these two tables is ...
Hi FrancescoSCP ,
sorry, I did not fully understand this part in my first answer.
You can adjust your DAX formula to calculate the correct subtotals:Sum of value = VAR _fullSum = SUM ( Data[Value] ) VAR _partialSum = CALCULATE(SUM(Data[Value]), 'Balance Sheet'[Sign] <> "") VAR _sign = IF ( AND ( CONTAINS ( 'Balance Sheet', [Sign], "A" ), NOT ( CONTAINS ( 'Balance Sheet', [Sign], "D" ) ) ), "A", IF ( AND ( CONTAINS ( 'Balance Sheet', [Sign], "D" ), NOT ( CONTAINS ( 'Balance Sheet', [Sign], "A" ) ) ), "D" )) RETURN SWITCH ( _sign, "A", IF(_partialSum > 0, _fullSum - _partialSum, _fullSum), "D", IF(_partialSum < 0, _fullSum - _partialSum, _fullSum), _fullSum )
Hi AMeyersen , It seems to be working. Could you explain to me the formula in the various steps?
Hi FrancescoSCP , sure 🙂
- _fullSum => sum without any adjustments
- _partialSum => sum of signed accounts (A/D)
- _sign => If the current filtercontext contains a signed account (A/D) of one type but not of both, the value needs an adjustment.
- SWITCH => Sign type "A": If the sum of signed accounts is > 0 we need to exclude this value. Sign type "B": If the sum of signed accounts < 0 we need to exclude this value
The last part sets the value on signed accounts conditionally to zero and does the same adjustment to the subtotals.
If a sub(total) contains accounts of both types or no signed accounts at all there's no need for adjustments and therefore we can use the _fullSum.
Perfect thanks! I will test this soon on the full model!