Measure to sum column values based on another table, based on yet another table

[phuonganhtdinh]

Hi all,

Part of my data model consists of 3 tables below. I'm trying to create a measure that sums SalesAmount for projects whose ProgramIDs can be found in Dim_Program (in Dim_Projects, there are several ProgramIDs that do not appear in Dim_Program, and some ProgramIDs that do). Not sure how to write the formula.

Hope this makes sense; I'm very new to DAX. Thanks in advance!

1. Dim_Program

ProgramID	Program Name
aaa	Program1

2. Dim_Projects

ProjectID	ProjectName	ProgramID
100	Project1	aaa

3. tblSales

ProjectID	SalesAmount
100	500

[Jocke]

As long as you have the relationships properly set ut between your tables you shouldn't need to create a measure visualize the Sales amount / program.

By proper relationship I mean:

Dim_Program 1-->M Dim_Projects & Dim_Projects 1-->M tblSales

After you made sure that's done you should be able to drag the sales amount and program name into a visual and get the desired result.

[phuonganhtdinh]

Thanks a lot for the reply! @Jocke 

I do have the relationships set up like that. I still need this in the form of a Measure as I need to subtract this measure from another measure to arrive at... another measure (all of these measures are used separately in different charts). Could you recommend an appropriate formula?