Adding through multiple criteria

[Syndicate_Admin]

Hello everyone.

Of course thank you for any help. I am struggling to do the following: I have the challenge of adding up the amounts of an expense, but I must do it through several criteria. The main catalog has two columns, ACCOUNT | KEY1 and this one emerges several possible combinations, the image that I attach can help to understand what I want to do, what is in red is what would correspond to the expense.

Could I do it with DAX?. I have three valid cases to take the expense:

1. ACCOUNT y KEY1

2. Solo ACCOUNT

3. Key1 only

In the example the total would be $670

Thanks for any help

[isabella · Accepted solution]

Hi @Syndicate_Admin 

About your first criteria ,you can merge the two table like this in power query .

Then expand table like this  :

Now table is like this

Regarding to your second criteria,base on the upper ,still merge

Then expand like this:

Now the table is like this:

And about the third criteria , same as upper ,but this time merge base on key1 field

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Syndicate_Admin , You can have a calculated column in the second table

sumx(filter(Transact, Transact[key1] = catalog[Key1]), Transact[Amount])

or

sumx(filter(Transact, Transact[key1] = catalog[Key1] && Transact[Account] = catalog[Account] ), Transact[Amount])