Power pivot referencing tables in calculate formula

[daysleeper15]

Thanks in advance for any help on this one. I have the following three tables in my data model: 1. "Data Table" contains stacked trial balance information for 200 companies, each company has about 600 unique accounts, 2. "Control Table" contains the list of companies that I need to look at; and 3. Adjustments Table contains a list of accounts that I need to reverse out of the trial balance.

I set up a pivot so that I can filter by the company in the Control Table and bring in 3 column from the Data Table, i.e, account #, account description, and account balance.

I am now trying to create a fourth column for adjustments which would just show a negative of the balance in column 3 if the account# in column 1 is on the list in the Adjustments table.

Here is what I did to try and make this work. I added a column in to the Data Table and set it equal to -2019 balance. Then I thought I would be able to create a new measure =CALCULATE(Data[Negative 2019],Data[Account #]=Adjustment[Account #]). The first problem is that while I can reference the Adjustment table in the formula I don't get any option to choose a column.

So to test if this would even work at all I just selected 1 account# and wrote this formula CALCULATE(Data[Negative 2019],Data[Account #]="643290"). The formula works in that it picks up the reversal of "643290", however when I bring it into the values of the pivot table it lists the negative amount in every row where I only want it to show up in the 1 row for account "643290".

Any thought on how I can make this work? In excel I would just simply say if(vlookup(Data! A1, Adjustment! A1:A20, 1, false), -Data! A1, "")) and drag that formula down.....but I would really like to get this into Power Pivot.

Thank you

 

[Jdh173]

You could wrap it in an IF formula and write "if [my formula] value < 0, then "", otherwise [my formula]"