PowerPivot - New Measure - Calculated Field - Two Tables

[BobbyBull2009]

Hello,

I am using Excel 2010 and have difficulties to do a calculation (whether using measure or column).

My database is as follow:
I have two tables: System_1 and System_2
Each table has several accounts (rows) with a specified value (column)
The tables (or systems) are not the same - the one table could have accounts which the other does not, and if both Systems have a specific account it could be that the value differs.

What I want is the following:
A PowerPivot table stating the accounts (rows) of the combined systems (tables) and four columns:
Value of System_1
Value of System_2
The difference between the systems should both systems have the account
The remaining difference (i.e. where one of the system does not have the acccount)

I have created a PowerPivot table whereby I combine the two tables and also give me the vales of both systems in the columns.
However, I am struggling to create a calculation as mentioned above which will also be part of the PowerPivot table (as the table might change)

If it was a formula in Excel I would have used a simple formula such as IF(AND(System_1<>0,System_2<>0),System_1 - System_2,0) and the last formula as System_1 - System_2 - Delta (as calculated by the previous formula).

Any help would be appreciated. Thanks!!

Note: I have only recently started with PowerPivot, so my knowledge of all tabs and lingo is limited.

 

[scottsen]

It may be possible to write the DAX just right, but ... in general, you really want *1* common lookup table has has all the possible distinct values in both tables.

 

[BobbyBull2009]

Hi Scottsen,

Thanks for your post.

I managed to get the DAX formula just right so it does exactly what I want, however...
I have two row labels: the summary label (Cost Position, e.g. Revenue) and the detail label (Accounts, e.g. 12345678). I only require the cost position value, which is in the form of a subtotal. The problem is that the subtotal also applies the measure and does not summarize the detailed accounts as it should.

Is there a way around this or should I follow another approach? Is there maybe a way to consolidate two tables in the PowerPivot database seeing that it is much easier to write a formula in the database than in the PivotTable?

Thank you in advance.

 

[scottsen]

Oh, these are basically the "same" data, and can be combined? even better

What is the source of your data (csv/xlsx, sql, ...?) and when you are done with the report... does it need to land anywhere (say, Sharepoint?). In general, Power Query is a great option here. But typically there are other options as well, depending on your situation.

 

[BobbyBull2009]

Yes, it is basically the same data in two different tables. The source of my data is Access (accdb).

The Report does not have to land anywhere - it serves only as an calculation where I use the cost position summaries in the same Excel file.

In the Access table I have the following columns in the two tables:

Table1
System1_Account (e.g. DB00/12345678)
Cost_Position
Value1

Table2
System2_Account (0010/12345678)
Cost_Position
Value1

I connect my PowerPivot databases to these tables an create a calculated field in each table to give me the comparable account (i.e. 12345678). I then create a PowerPivot table with these two value columns, and also create a measure to give me the difference between the system (but only when there is a value in both systems/columns). The problem is that the measure is also applied to the subtotals (in this case the cost positions). I have found a solution but it is far from optimal - I selected the cost position (first) and accounts (second) as rows and the I sumif the totals referring to the cost positions. This works but leads to a long list or big table.

I can imagine two ways to improve it:
1. Change the table's settings to not match all accounts to each cost type, meaning not to list all accounts below each cost position but only list the accounts which is relevant to the cost position. It appears the measure which I created causes this problem.
2. Change the table into a more list view (i.e. split the column into two columns - one for the accounts and one which identifies the cost position). Currently it gives the account and list all cost positions next to it (although only one can be true).

Hope I explained the Problem good enough :/

 

[scottsen]

Not that you are excited (probably) about throwing another new technology into your solution, but a bit of Power Query would really help here. I'm sure that is a way to union/concat the data directly in Access as well, but... while I'm great at SQL, I am not at Access

But if you use Power Query, it is super easy to append these 2 tables together into 1, then continue your party in Power Pivot.