Using Power Pivot with QQube for Flexible Profit/Loss Statements
Generating financial statements can be tedious due to its standard formatting does not lend itself to being pivot table friendly. One issue is that Profit/Loss statements often are shown with Revenue and Expenses as positive numbers. When in reality, Revenue is a credit. Clients want to see Revenue as a positive number as well as expenses. This poses a problem for Traditional Pivot tables as the column Grand Totals and Subtotals only Add. So a schema where Revenue is a Credit and Expense as a debit (or Revenue is a credit and Expense are Credits) calculates correctly for Net Income .
Clients don’t like it .
The Other issue that Clients want to usually see Current Month, YTD ,Budget numbers and Budget to Actual Variance. Without Power Pivot, this would require generating some special calculated columns in your tables to identify these values.
But with Power Pivot, both of these can be resolved. As the subtotals on a pivot can display separately from the detail lines. And the “Date” functions in Power Pivot are far more powerful than traditional Excel.
In generating the examples below, I used QQube as my QuickBooks data source.
With QQube, (www.clearify.com) you can pull QuickBooks data from many different viewpoints. QQube is the Small Business Analytics source for QuickBooks.
These measures relate to the “GL Detail” subject from QQube. Within the GL Details subject – GL Line Amount and Budget Line Amount is shown as a Credit for Revenue and a Debit for Expense.
***Clients want Income and Expense to display as positive numbers.******
The following Power Pivot measures were developed to meet client needs.
Main Power Pivot measures used:
GL Amount – This measure flips the sign on Income/Other Expense detail lines and flips the signs for subtotals/grand totals. This way Income will display positive at the line level and at the subtotal level. Same for Expenses. But for the Gross Profit calculation, Income, under the hood is positive and expenses negative, giving you a correct Gross Profit result.
=if(hasonevalue(‘GL Details’[Account Type]),
if(values(‘GL Details’[Account Type]) = “Income” || values(‘GL Details’[Account Type]) = “Other Expense”,
sum([‘GL Details’[GL Line Amount])*-1), sum(‘GL Details’[GL Line Amount])),
sum(‘GL Details’[GL Line Amount])*-1)
What it means: If one detail row – Account Type is Income or Other Expense – then Sum(GL Line Amount *-1), otherwise Sum(GL line Amount)…..if at subtotal or Grand Total then (Gl line Amount*-1)
Note: this is your Month measure
GL AmountYTD – this measure calculates the YearToDate Amount I love this measure!
What it means: Calculate the YTD amount using the Calendr(Date) field
This will calculate the correct amount no matter what MONTH or YEAR you choose on Slicers
Note: if client is on a fiscal YTD, then add the Fiscal Year End at end of Measure…
=Calculate([GL Amount],DATESYTD(Calendr[Date],”5/31/2015”) then formula will calc correctly for a Fiscal Year end of “5/31″
Variance – Budget less Actual
=sum(‘GL Details’[RowVariance]) To make the ‘Budget – Actual Variance’ come out correctly, I generated a calculated column in Gl Details called “Row Variance”( Budget Line Amount – GL line Amount)
Budget Amount: Add Budget measures similar to above for Budget Line Amount.
Result is what you see below:
Fran Reed – FreedupDataOptics – 4/5/2016