Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
5 years ago

Mapping different depending on total sum

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 DimGLClassific...
Need Help
lekkerbek's avatar
lekkerbek
Helper IV
5 years ago

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's avatar
    DataInsights
    Super User
    5 years ago

    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's avatar
      lekkerbek
      Helper IV
      5 years ago

      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.