Power Pivot Archives – The BIccountant

Memory efficient clustered running total in Power BI

Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:

Problem

The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:

Besetzung =
CALCULATE (
    SUM ( Fact[Entries] )
– SUM ( Fact[Exits] );
    FILTER (
        ALLEXCEPT ( Fact; Fact[JourneyID] );
Fact[StopId]
<= EARLIER ( Fact[StopId] )
    )
)

Solution

In the query editor, I grouped the fact-table by “JourneyID” and choose “All Rows”:

Group in the query editor to effectively partition your Fact-Table

Due to the fact that the fact table was sorted by the “JourneyID”, I could use GroupKind.Local and manually tweaked the resulting code like so :

Table.Group(#"Changed Type2", {"JourneyID"}, {{"All", each _, type table}}, GroupKind.Local)

Without this option, the calculation would have also errored out.

This effectively created table partitions, with one row for each “JourneyID”:

One table per JourneyID

I then added a custom column where I used my superfast M-code for running totals, referencing the column “All” with the partition in it:

Table.AddColumn(#"Grouped Rows", "Custom", each fnRT([All], "JourneyID", “Movements”))

Function Code

This M-code for running totals is amazingly fast (within M-dimensions 😉 ) and particularly plays its strengths on large sections for aggregation.

Performance

Memory consumption rose from around 1,700 MB to 2,000 MB, all very stable. Of course, refresh duration rose (by 150 % for that table). But for that use case, it was an acceptable price to pay.

Enjoy & stay queryious 😉

DAX evaluation context indicator with syntax highlighting

This is a post about a feature request, that definitely would make my life easier (and I guess others’ too):

Evaluation context and its transition is one of the hardest concepts in DAX and I would find it extremely helpful, if syntax highlighting would assist us here. Let the colour of the table name show whether the expression will be evaluated:

Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports

Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on 🙂

As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:

Right aligning text (please vote for it here: Right align text in Power BI – edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)

So for the moment I choose between the following workaround-options:

Display %-values in a separate column
Format numbers as text and fill up with spaces so that all end up right aligned
See the suggestion from Matt Allington in the comments below (very nice)

Right aligning text or percentage figure in new column

For both options the preparations are the same:

KPIs in Easy Profit and Loss for PowerBI

Welcome to the last part of my Easy Profit & Loss series where I will cover KPIs in rows & columns:

1) KPIs in columns

Show all your figures as a percent of turnover for example: Nice & easy: Divide current figure by the total sum of turnover:

Turnover% =
ABS (
    DIVIDE (
[ActSign],
        CALCULATE (
[ActSign],
            FILTER (
                ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report] = “Income”
            )
        )
    )
)

We need to leave the current row context to retrieve the turnover-value in each row, therefore the ALL.

2) KPIs in rows

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)

Switching signs in Power BI and Power Pivot without unary operators

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:

Table “Accounts”

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

Comfort Functions for Easy Profit & Loss statements in Power BI and Excel – Part2

Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂

No need to specify single accounts

So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:

Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown

No more specification of individual accounts

How to use it:

How to use Individual Report Layout

2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)

No need to define individual group items