Monday, August 10, 2020

How to use grouping in Power BI and apply group to the DAX expression

 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? 

  1. Highlight the TypeID, right-click and choose New group
  2. Set the Group name (TYPEIDGROUP for example)
  3. 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
  4. Select remaining values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Debit name. 
  5. 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