Forum Discussion

phuonganhtdinh's avatar
phuonganhtdinh
Icon for Helper I rankHelper I
6 years ago

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

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

ProgramIDProgram Name
aaaProgram1

 

2. Dim_Projects

ProjectIDProjectNameProgramID
100Project1aaa

 

3. tblSales

ProjectIDSalesAmount
100500

2 Replies

  • Jocke's avatar
    Jocke
    Regular Visitor
    6 years ago

    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's avatar
      phuonganhtdinh
      Icon for Helper I rankHelper I
      6 years ago

      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?