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
    )
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

        Perfect thanks! I will test this soon on the full model!

         

  • 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