topic Re: Calculate sum with filter in different table in DAX Commands and Tips

Calculate sum with filter in different table

Sparkeltje — Thu, 16 Jul 2020 13:18:39 GMT

Recently started with DAX.

I want to calculate a column in a table for the current year. I got that one.

But I really only want to add up the amounts in table 1 to projects that meet the conditions in table 2 (status and type) for the current financial year.

 
Table 1
columns:
project
amount

Table 2
columns:
project
status
type


Thanks in advance for your help.

Re: Calculate sum with filter in different table

lbendlin — Thu, 16 Jul 2020 13:43:55 GMT

Do you want to do this via the data model or via lookup?

Re: Calculate sum with filter in different table

Sparkeltje — Thu, 16 Jul 2020 13:50:43 GMT

Via data model.

Re: Calculate sum with filter in different table

Ste_For94 — Thu, 16 Jul 2020 13:52:05 GMT

Let me understand what you mean:

you want to sum amounts from table one for all projects that have a certain status and type.

this is easy: you can simply create a relation between the two tables, then you can use a DAX expression like

Calculate(Sum('Table1'[Amount]), 'table2'[status]= your condition, 'table2'[type]= your condition)

now the problem is: do you want this to be an added column in a table, do you want to create a new table or do you want this to be a measure?

Hope I was Helpful. if I did please mark as solution and kudos are always appreciated 😉

Re: Calculate sum with filter in different table

Sparkeltje — Thu, 16 Jul 2020 14:15:47 GMT

It is that simple! Found out what I did wrong. Something with ( "" etc.