Forum Discussion

FrancescoSCP's avatar
FrancescoSCP
Helper IV
2 years ago
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 ...
Need Help
  • AMeyersen's avatar
    AMeyersen
    2 years ago

    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
    )
AMeyersen's avatar
AMeyersen
Resolver III
2 years ago

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
    )
FrancescoSCP's avatar
FrancescoSCP
Helper IV
2 years ago

Hi AMeyersen , It seems to be working. Could you explain to me the formula in the various steps?