Forum Discussion
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...
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.
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).
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.