Powerpivot DAX measure: sum “if then”

Question

I am looking at some intercompany accounts receivables (AR) data. Let's assume, I have a couple companies that have transactions with each other. All but one of those companies use the same accounting system where I can retrieve the data from. For the odd one out company, I do not have access to AR data of intercompany transactions. As a proxy, I take the accounts payables (AP) data from the other companies. My aim is to prepare a report showing the total AR data for all companies (built from the AR data of the of companies on the central accounting system and of the AP data that those companies have booked against the one company)

I now have three tables in Powerpivot, "table AR" holding AR data (company code, AR account numbers and the amount) and "table AP" holding accounts payable data and a "look-up table" holding the company codes, the respective AR codes and a column telling me wether the company is on the central accounting system or not (this column simply holds the information "direct" or "indirect" - reflecting the way I gather the data)

Whenever the company code is linked to "direct" in the look-up table, my measure should sum up the amounts from the AR table filtered by column "company code". When the company code is "indirect", the measure should sum up the amounts from the AP table filtered by column "AP number".

The idea would be to build a DAX measure that sums up the AR amounts along these lines:

If company(in "look-up table") = "direct" then use the company code and sum all amounts in "table AR" when match in column "company code" of "table AR".

Else if company("look-up table") = "indirect" then use AP-account-number from "look-up table" and sum all amounts in "table AP" when match column "AR account no."

Maybe the picture below can clarify my idea a bit further.

I'd be very grateful for some ideas how I can build a DAX measure for the above.

Thanks a lot!

enter image description here

Jacob Jacob · Accepted Answer · 2014-12-05T12:52:58

This is a great use of PowerPivot and although there was something about your dummy data that confused me a little, this should answer your question.

I'm assuming that your AR and AP tables have relationships to your lookup table on company code and AR acc no respectively. This means that both columns must be unique in the real world!

In theory you could write this as one mega measure but that isn't great practice - I'll break this down into 4 measures.

Firstly a measure for summing the amounts in each of the AR and AP tables:

[AR Amount] = SUM(AR[amount])
[AP Amount] = SUM(AP[amount])

This next measure does the IF portion and also deals with situations where the company code has more than one value (i.e. totals which would cause an error). The key here is that you can't put a 'naked' column reference in a measure so you use VALUES on the access column:

[Raw Measure] =
               IF (
                   HASONEVALUE ( lookup[company code] ),
                   IF (
                       VALUES ( lookup[Access] ) = "Direct",
                       [AR Amount],
                       [AP Amount]
                       ),
                    BLANK ()
                   )

The problem here is your totals - maybe you care and maybe you don't but the measure above would make them blank! The following measure iterates over the company codes and then sums up the values for the totals (would work for subtotals too if you have them):

[Iterated Measure] = SUMX(VALUES(lookup[company code]), [Raw Measure])

Powerpivot DAX measure: sum “if then”

1 Answers