Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
FrancescoSCP
Helper III
Helper III

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 the account code.

There are duplicate accounts because, based on the balance of the movements for that period, the data should be displayed on one account rather than another.

For example, the "bank" account may be displayed under "liquidity" if the balance is positive, while under "liabilities" if the balance is negative.

Expected behaviour is this:

FrancescoSCP_1-1713942759132.png

 


As you can see, in month 1 value of Acc. Code "10200" is -50 so it is displayed only in Liabilities. On month 2 the value is +70 so it is displayed on Liquidity and so on. The total value, for the three selected months, is -50+70-30 = -10 so it's displayed only on liabilities.

The balance value depends on the user period selection and i can't precalculate it because he can select any combination of month-year.

I'm not sure how to handle this with a many-to-many relationship.

I've tried using measures, but the subtotals are incorrect.

 

Do you have any ideas?

Here you can find a .pbix files with demo data and what i've done
https://we.tl/t-1F9zEUGvxt

 

Thank you.

 

 

1 ACCEPTED SOLUTION

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
    )

View solution in original post

7 REPLIES 7
AMeyersen
Resolver I
Resolver I

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

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

 

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

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.

FrancescoSCP_0-1714742222355.png

The behaviour in the single month is perfect.

 

Any ideas?

Regards,

Francesco

 

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!

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.