Sum with filters and lookup?

[dunnobe]

Dear all,

I'm trying to calculate our "real spend" by deducting various expenses from our total spend. (Total costs minus HR costs, branches, partnerships etc).

I had written this amateurish, but "working" piece of formula that is now giving me an error after updating my data sources (excel files):

My measure:

_ Sum EKPO Partners:=CALCULATE (SUM(f_EKPO[Nettobestelwaarde]);
FILTER (f_EKPO; f_EKPO[EKPO_leverancier]<> "9996");
FILTER (f_EKPO; f_EKPO[Goederengroep]<>"S44411000");
FILTER (f_EKPO; f_EKPO[EKPO_leverancier]<> "9449");
FILTER (f_EKPO; f_EKPO[Goederengroep]<>"S61507000");
FILTER(f_EKPO;LOOKUPVALUE(f_EKKN[EKKN_GBR];f_EKKN[EKKN_key];f_EKPO[EKPO_key])<>"OUT");
FILTER(f_EKPO;LOOKUPVALUE(d_partners_filialen[Status];d_partners_filialen[Crediteur];f_EKPO[EKPO_leverancier])="Partners")
)

My goal is to calculate the [Nettobestelwaarde] for each row in table f_EKPO that meets certain conditions.

The sum may NOT include this:

• rows with value 9996 and 9449 in column f_EKPO[EKPO_leverancier]
• rows with value S61507000 and S44411000 in column f_EKPO[Goederengroep]
• rows that have the value "OUT" in table f_EKKN[GBR]
• rows that have the value "Partners" in table d_partners_filialen[Status]. 

The reason why I'm excluding these values is that I have already calculated their sum in previous measures. I have no idea if this is the most efficient way to trickle down from total costs to our "real spend", but it seems to work...

My relations:

Is anyone able to guide me how to proberly exclude a few values by filtering and doing lookups?

Thanks all

Best regards,

Bram

[dunnobe]

Hi guys, it appeared that a value appeared more than once in my lookup table, thus giving me an error. I guess I've figured it out.