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 ,
I'd remodel the data structure to avoid complex relationships and DAX statements.

  1. Create surrogate keys in table [Balance Sheet] to have a unique identifier for both [10200 - Liquidity] and [10200 - Liabilities]
  2. Transform 'Data'[Acc Code] to match the new surrogate keys. Distinquish between positive and negative values.
  3. Have a simple 1:n relationship and a simple measure SUM('Data'[Value])
  • FrancescoSCP's avatar
    FrancescoSCP
    Helper IV
    2 years ago

    Hi AMeyersen ,

     

    i can't do this because i don't know on single line on the fact table if the balance of that Acc Code is negative or positive based on the user selection.

    Regards,

    Francesco

     

    • 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?