Guide for switching Signs in Power BI and Power Pivot (bypassing Unary Operators in DAX)

In finance & accounting, you very rarely report the figures with the signs of their source systems, but switch (certain) signs according to different needs. Instead of using unary operators for it, I’ll present an easy and dynamic way for it in Power BI and Power Pivot using DAX. It will cover the following 3 main scenarios:

• 1_SwitchAll: All signs are switched (red)
• 2_SwitchExpLiab: Expenses and liabilities are switched back to their original values (green)
• 3_BWT_Indiv: Only the main figure for expenses (or liabilities) carries a minus, all following positions specifying the expenses are (principally) reported as positives (blue)

 

I’m using the sample data from this article but changed the source-data to a double-bookkeeping structure. There signs are used and the transaction entries in your ledger table always add up to zero. This is a method that prevents errors when posting and can also be used to prevent errors in reporting. If you keep the signs in your reporting system, all you have to do is add up the relevant figures and the returned (absolute) figures will always be correct. If you have read my previous articles on Easy P&L, you have seen this method in action: No minus-operation there, just a simple stupid adding of all accounts who fall into several (sub-) total categories via the bridge-table.

The Account-table also contains of (sub-) totals and the column “AccountType” shows if the positions are regarded as Turnover (Revenue) or Expenses:

1_SwitchAll

My values on “1_SwitchAll” corresponds to “FinalValue” in the article above. The revenues come from consultancy and coursed provided. But the revenue for courses don’t just consist of attendee rates, but the costs for catering and paid instructors shall be deducted (highlighted in yellow). So the “good” numbers that contribute to cash in your pocket shall be reported without a sign and the “bad” numbers that result in an outflow of cash shall be reported with a minus. Within the expenses category, the costs carry a minus and the travel refunds (highlighted in orange), which are cash positive, are reported as positives.

2_SwitchExpensesLiabilities

Another requirement that is often used for balance-sheet-reporting or reports that only report on cost-situations, require that the costs or liabilities are reported without signs. … Principally, because the reimbursements/cost deductions shall be reported with an opposite sign (to show the adverse effect to the cashflow). This is what “2_SwitchExpLiab” shows (not covered in the article).

3_BWT (“BossWantsThat”)

Last but not least comes a typical “BossWantsThat”-requirement: Basically some strange stuff that you just have to deliver. Here the main categories “Revenues” and “Expenses” shall be shown with the signs that reflect the cash-direction, but all specifications that follow below shall be reported without signs (again: Principally, because positions with opposite cash-effects than the main category shall carry inverted signs).

Reporting techniques covered with this approach

These reporting techniques shall be covered:

1. Symmetrical reporting using group tables with pivot tables or all other kinds of tables in Power BI (incl. new Matrix report: Check the file enclosed)
2. Easy P&L with (account) group tables from my previous posts
3. Easy P&L with parent-child-hierarchies (see how to flatten them here)

The Measures

The first 2 measures work for all scenarios:

1_SwitchAll = [Sum]*-1

 

2_SwitchExpLiab = [1_SwitchAll]*[SwitchSignExp]

with

 [SwitchSignExp] = if(DISTINCTCOUNT(Accounts[AccountType])=1 && MAX(Accounts[AccountType])="Expenses", -1 , 1)

For the [SwitchSignExp] we want to identify all items that carry only “Expenses” (and nothing else) and return a -1 for the sign-invertion. So as soon as “Revenue”-figures will also be included, a 1 for maintaining the existing sign shall be returned. The 2 following conditions on the Accounts-table (that is part of all 3 scenarios) do what’s needed:

• MAX(Accounts[AccountType])=”Expenses”) will return true if only “Expenses” are included
• DISTINCTCOUNT(Account[AccountType]) = 1 will return true if only one distinct value is included (which must be “Expenses” according to the previous condition)

For the 3rd measure I didn’t find a solution that works for all, so we need to define individual Switch-measures that reference the group- or bridge-tables:

1. BWTSwitch = if(MAX(DimAccountGroups[AccountType])="Expenses" && COUNTROWS(FILTER(ALL(DimAccountGroups), DimAccountGroups[AccountType]="Expenses"))=COUNTROWS(DimAccountGroups), -1,0 ,1)

2. BWTSwitch_IndAcc = if(MAX(BridgeTableIndividual[AccountGroupKey])="AccountType - Expenses", -1,0 ,1)

3. BWTSwitch_PC = if(MAX(BridgeTablePC[AccountId])=8 , -1,0 ,1)

You apply them all the same way: 3_BWTxx = [2_SwitchExpLiab]*[BWTSwitchxx]

I’ll skip the description on those – until someone needs them and asks for it 😉

Underlying datamodel covering all techniques

Summary

For the typical finance- & accounting reports you don’t need to apply clumsy unary operators, but can use the above mentioned dynamic techniques. Make sure to operate on your original accounting-data with signs. That’s not only the prerequisite for these techniques to work, but also a best practice, as you eliminate risks like applying the wrong sign when aggregating your data.

Don’t miss the last part of this series, where I will show how to include KPIs and traffic lights in your reports: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/

Download for logged-in subscribers:  UnaryOperatorsUpload2.zip

Enjoy & stay queryious 🙂