April 9, 2013 Leave a comment
While evaluating the possible migration of an accounting/financials cube from Multidimensional to Tabular i came across these posts which helped me make a hyper-jump forward.
- PowerPivot and Parent/Child hierarchies – Alberto Ferrari based on pre PATH() PowerPivot
- PowerPivot, Parent/Child and Unary Operators – Alberto Ferrari
- PowerPivot Denali: Parent child using DAX – Kasper de Jonge
- Parent/Child Hierarchies in Tabular with Denali – Alberto Ferrari
This post is an attempt of variation of the technique explained in PowerPivot, Parent/Child and Unary Operators to simulate the Multidimensional Unary Operator. It starts from a slightly different initial situation, where additionally to the Unary Operator for each Account, we know something about the account type itself, in this case if the account expects normally Inflows or Outflows. This is an information which usually is available in this type of setup.
This post builds on the example given in Alberto Ferraris above mentioned post and the techniques explained there in detail are not repeated here.
The basic idea of the approach is to separate calculation from presentation, not using the Unary Operator to make the calculation but just to correct the sign of the calculated amount to suit the user requirement.
We start with a slightly different data set, adding Accounts.InOutFlow representing the expected direction of the cash flow. The AggPaths query from the original dataset we will not need.
CREATE TABLE Accounts ( AccountId INT, Account VARCHAR (100), ParentAccountId INT, Aggregator CHAR(1), InOutFlow char(3) ) INSERT INTO Accounts (AccountId, Account, ParentAccountId, Aggregator, InOutFlow) VALUES ( 1, 'Final Total', NULL, ' ', 'In'), -- Grand Total ( 2, 'Revenues', 1, '+', 'In'), -- ADD Total Revenues to Grand Total ( 3, 'Courses Provided', 2, '+', 'In'), -- SUM Courses to Revenues ( 4, 'Instructors paid', 3, '-', 'Out'), -- SUBTRACT paid instructors from Courses ( 5, 'Cathering', 3, '-', 'Out'), -- SUBTRACT cathering from Courses ( 6, 'Attendees rates', 3, '+', 'In'), -- SUM attendee rates to Courses ( 7, 'Consultancy', 2, '+', 'In'), -- SUM Consultancy to Revenues ( 8, 'Expenses', 1, '-', 'Out'), -- SUBTRACT Expenses to Total ( 9, 'Travel', 8, '+', 'Out'), -- SUM travel to Expenses (10, 'Travel refund', 9, '-', 'In'), -- SUBTRACT travels refund to Travels (11, 'Travels paid', 9, '+', 'Out'), -- SUM travels paid to Expenses (12, 'Courses Taken', 8, '+', 'Out'); -- SUM courses taken to Expenses
Now in PowerPivot i added 2 calculated columns: LeaveMultiplier and Multiplier.
LeaveMultiplier formula is:
LeaveMultiplier=IF(Accounts[InOutFlow] = "Out", -1, 1)
and will return a 1 or -1 to multiply the Transactions[Amount] value in order to get a signed amount which aggregates correctly. It means, all Inflows are considered finally positive and all Outflows are considered finally negative. No matter what the accounts hierarchy models, the Final Total must always be the simple sum of these “signed” values. The “leave” in the name is because the Transaction[Amount] values are only recorded at leave level of the hierarchy, and this signing of amounts is conceptually only relevant at that level as later the amounts aggregate “naturally” up the hierarchy.
With this LeaveMultiplier we can make a measure:
NaturalSumOfAmounts:=IF ( Accounts[ShowRow], SUMX (Transactions, Transactions[Amount] * RELATED(Accounts[LeaveMultiplier])), BLANK() )
which will give:
Now, while this sums up correctly the Final Total, all the nodes below Expenses do not show the sign that is required. So, now its the moment to work on the presentation of the values.
For this i created a field Multiplier which I will use to show the node aggregates following the requirement in Accounts[Aggregator]:
Multiplier=IF( (Accounts[InOutFLow] = "In" && Accounts[Aggregator] = "-") || (Accounts[InOutFLow] = "Out" && Accounts[Aggregator] = "+"), -1, 1 )
It says: if it is an Inflow and it should be shown as a negative value, then invert the value. Same if it is an Outflow and it should be shown positive. In all other cases, leave the value as it comes. If we can apply this to the “natural” values shown before the final presentation should be ok.
To achieve this we actually need for each node, at measure calculation time, the Multiplier value. This seems to be a very tough task as we are mixing calculation time with filters with row level. It can be solved in a hierarchy using the ISFILTERED() function and the technique explained in Parent/Child Hierarchies in Tabular with Denali to detect the “BrowseDepth”.
As preparation, we will create additional fields to expand the Multiplier to the different levels of the hierarchy. This supposes that we built the hierarchy following this post looking like:
We want to achieve:
using formulas like:
Level2Multiplier=LOOKUPVALUE( Accounts[Multiplier], Accounts[AccountId], VALUE(PATHITEM ( [Path], 1 )) )
This will repeat the Multiplier value in a different format, bringing it from row level to a multi-row format, which will allow us to use a measure together with the ISFILTERED() formula to get the right Multiplier value for each node.
The measure looks like:
IsFilteredMultiplier:=IF ( ISFILTERED ('Accounts'[Level4]), MIN(Accounts[Level4Multiplier]), IF (ISFILTERED ('Accounts'[Level3]), MIN(Accounts[Level3Multiplier]), IF (ISFILTERED ('Accounts'[Level2]), MIN(Accounts[Level2Multiplier]), IF (ISFILTERED ('Accounts'[Level1]), MIN(Accounts[Level1Multiplier]) ))))
where the MIN in MIN(Accounts[Level4Multiplier]) is just to use some aggregate function as all values will be the same (we could also use MAX for example).
At this point we can check if IsFilteredMultiplier is working:
Finally with a measure like:
NicerSumOfAmounts:=IF ( Accounts[ShowRow], SUMX ( Transactions, Transactions[Amount] * RELATED(Accounts[LeaveMultiplier]) ) * Accounts[IsFilteredMultiplier], BLANK() )
we will have:
which fulfills the original request.
If this technique works well (hope to find out soon) then it could be used as basis for migrating Multidimensional cubes modelling Chart Of Accounts situations that rely on Unary Operator and where we have information about the type of the Accounts in relation with its expected sign. Or in accounting terms if we know for each account the expected “side”, either Debit or Credit, and how it should be shown in the pivot table.
The PowerPivot file is available as UnaryVariant2.