Let's say you have field that contains the fixed list of values and you want to group them. In my scenario I have TypeID field that contains the following values: Revenue, Liability, Equity, ProfitandLoss, Expense, BalanceSheet, Asset, Total. In my case, Revenue, Liability, Equity corresponds to Credit and the rest to Debit. What can I do?
- Highlight the TypeID, right-click and choose New group
- Set the Group name (TYPEIDGROUP for example)
- Select Revenue, Liability, Equity values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Credit name
- Select remaining values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Debit name.
- The result should be as presented on the screen
Now, you are able to connect to the group and use it in the formula. For example, I used it in the created measure:
Actuals credits positive =
CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],
FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Debit" )
- CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],
FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Credit")
Another option is to use IN expression
Labor =
CALCULATE
(
FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actual operating]*-1,
FinancialReportingTransactionData_MainAccount[VALUE] in {"600300", "600400"}
)
It's up to you to decide which approach to use.
No comments:
Post a Comment