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?

       

      • AMeyersen's avatar
        AMeyersen
        Resolver III
        2 years ago

        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.

         

    • FrancescoSCP's avatar
      FrancescoSCP
      Helper IV
      2 years ago

      Hi AMeyersen, 

      i need a different behaviour on column total, it has to be the sum of all months.

      So for example, total of Liquidity has to be 70, total of Liabilities has to be -80. And also subtotals, always on column total, has to be correct. For example Voice A1 total has to be 100+150+50 = 300.

      The behaviour in the single month is perfect.

       

      Any ideas?

      Regards,

      Francesco