Mapping different depending on total sum

[lekkerbek]

Hi,

I created a model to show a balance sheet. This model has a couple of tables:

DimGLaccounts -> GL number, GL description, BalanceType

DimGLMapping -> GL number, Mapping code

DimGLClassificationRelations -> the build up of the balance sheet

Transactions -> date, GL number, Amount, etc

In some cases GL accounts could be on the debit side of the balance sheet, but if the amount is negative it should be on the credit side of the balance sheet.

For example I have GL accountnumber 06520 which is a current account with a group company.

If the amount is positive it should be mapped to "receivables from group companies" which is:

E-B1010203

If the amount is negative it should be mapped to "payable to group companies" which is:

E-B10204

Is this somehow possible within Power BI? The problem is that is to calculate a total sum of a specific GL account from another table and then determine if it should be either this mapping or the other. This mapping could me different for multiple GL Accounts.

[lekkerbek]

@DataInsights 

GL Mapping = 
VAR vDebitMapCode =
    MAX ( DimGLClassificationMappings[Debet mapping] )
VAR vCreditMapCode =
    MAX ( DimGLClassificationMappings[Credit mapping] )
VAR vDebitMapping =
    CALCULATE (
        MAX ( DimGLClassificationReturns[Fin Stmt Line] ),
        DimGLAccountClassificationRelations[CODE_ATTR] = vDebitMapCode
    )
VAR vCreditMapping =
    CALCULATE (
        MAX ( DimGLClassificationReturns[Fin Stmt Line] ),
        DimGLAccountClassificationRelations[CODE_ATTR] = vCreditMapCode,
        USERELATIONSHIP ( DimGLAccountClassificationRelations[CODE_ATTR], DimGLClassificationMappings[Credit mapping] )
    )
VAR vResult =
    IF ( [Total Amount] > 0, vDebitMapping, vCreditMapping )
RETURN
    vResult

I'm not sure:

* what to put here: "DimGLClassificationReturns[Fin Stmt Line]" as my model is different and not sure what it does.

* how to build up the visual to leave the current setup in tact, but it just switches the mapping to the other side if needed.

[DataInsights]

@lekkerbek,

Try these measures. Delete the relationships between DimGLClassificationMappings and DimGLAccountClassificationRelations. 

Total Amount = SUM ( Transactions[Amount] )

Dynamic Amount = 
VAR vMapping =
    MAX ( DimGLAccountClassificationRelations[CODE_ATTR] )
VAR vAccountBalance =
    ADDCOLUMNS (
        SUMMARIZE (
            DimGLClassificationMappings,
            DimGLClassificationMappings[GL Account],
            DimGLClassificationMappings[Debit Mapping],
            DimGLClassificationMappings[Credit Mapping]
        ),
        "tmpTotalAmount", [Total Amount]
    )
VAR vAccountMapping =
    ADDCOLUMNS (
        vAccountBalance,
        "tmpMappingToUse",
            IF (
                [tmpTotalAmount] > 0,
                DimGLClassificationMappings[Debit Mapping],
                DimGLClassificationMappings[Credit Mapping]
            )
    )
VAR vAccountToSum =
    FILTER ( vAccountMapping, [tmpMappingToUse] = vMapping )
VAR vResult =
    SUMX ( vAccountToSum, [tmpTotalAmount] )
RETURN
    vResult

Create visual using DimGLAccountClassificationRelations[DESCRIPTION] (and additional columns from this table).

[lekkerbek]

Thank you @DataInsights. It's not exactly working the way I want it to be, but that has to do with my model I guess. I'm going to study this. Thanks for leading me in the right direction.

[DataInsights]

@lekkerbek,

This approach uses two mapping code columns in DimGLMapping:

Data model:

The inactive relationship is between DimGLMapping[Credit Mapping Code] and DimGLClassificationReturns[Mapping Code].

Measures:

Total Amount = SUM ( Transactions[Amount] )

GL Mapping = 
VAR vDebitMapCode =
    MAX ( DimGLMapping[Debit Mapping Code] )
VAR vCreditMapCode =
    MAX ( DimGLMapping[Credit Mapping Code] )
VAR vDebitMapping =
    CALCULATE (
        MAX ( DimGLClassificationReturns[Fin Stmt Line] ),
        DimGLClassificationReturns[Mapping Code] = vDebitMapCode
    )
VAR vCreditMapping =
    CALCULATE (
        MAX ( DimGLClassificationReturns[Fin Stmt Line] ),
        DimGLClassificationReturns[Mapping Code] = vCreditMapCode,
        USERELATIONSHIP ( DimGLClassificationReturns[Mapping Code], DimGLMapping[Credit Mapping Code] )
    )
VAR vResult =
    IF ( [Total Amount] > 0, vDebitMapping, vCreditMapping )
RETURN
    vResult

Note the difference in GL Mapping when the amount is positive vs. negative:

Let me know if this works in your data model. If not, please post a pic of your data model, desired result, and sample data (in a format that can be copied into Power BI).

[lekkerbek]

@DataInsights

Thank you so far for your help. I have implemented the debit and credit mapping, but I'm having trouble with implementing the measure. Please find attached screenshots of:

1) my model

2) partial details of my relations table

3) how my balance sheet matrix is setup

[DataInsights]

@lekkerbek,

Would you post your measure so I can review the DAX? Are you getting an error, or the wrong result?