Forum Discussion

carlenb's avatar
carlenb
Icon for Advocate II rankAdvocate II
2 years ago
Solved

DAX / Measure help on volume for multiple projects during different dates

Hi,

 

I have three tables.

 

  • Table 1 with column A = Project name, B = Sub project name, B = Price, column C = Quantity
  • Table 2 with column A = Project name, B = Sub project name, C = start date for projects, column D = number of projects
  • Calender table

I need to calculate the total volume (B*C) for each project (table 1), and then multiply this with the number of projects (project table, column I). A project can be divided into unique sub projects. Each project will start at different times, so there might be 2 project starts in November 2023, 5 at January 2024 etc. So in summary - for each project start, I need to total value of that project.

 

See below as an example. I would really appreciate help to create a measure in DAX on this.

 

 

 

 

  • some_bih's avatar
    some_bih
    2 years ago

    Hi carlenb possible solutions as following (order is important). Please note, no relationship is created for this solution

    create 3 different calculate columns (1 per tables: ProjectTable, Sales CTV KGH, Sales CTV PPC)

    1.  CTV KGH Value = 'Sales CTV KGH'[Price] * 'Sales CTV KGH'[Quantity]--sum is 200

    2. CTV PPC Value = 'Sales CTV PPC'[Price] * 'Sales CTV PPC'[Quantity] --sum is 104

    3.  CTV PPC KGH Rel =

    --calculating  related total from table CTV PPC, column Value (Price * Quantity)

    SUMX (
        FILTER (
            'Sales CTV PPC',
            'Sales CTV PPC'[Project] = ProjectTable[Project]
                && 'Sales CTV PPC'[Sub-project name] = ProjectTable[Sub-project]
        ),
        'Sales CTV PPC'[CTV PPC Value]
    )
    +
    --calculating  related total from table CTV KGH, column Value (Price * Quantity)
    SUMX (
        FILTER (
            'Sales CTV KGH',
            'Sales CTV KGH'[Project]= ProjectTable[Project]
                && 'Sales CTV KGH'[Sub-project name] = ProjectTable[Sub-project]
        ),
        'Sales CTV KGH'[CTV KGH Value]
    )

     

     

    Output CTV PPC Value

    Final output in ProjectTable

     

    Did I answer your question? Kudos appreciated / accept solution!

22 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi carlenb table 2 is "fact" table, the table with most details?

    Table 1 is just list of projects, price & quantity?

    If yes, why not one table (not 2 table 1 and table 2)?

    Price and quantity will be fixed, not variable during time?

  • carlenb's avatar
    carlenb
    Icon for Advocate II rankAdvocate II
    2 years ago

    Hi some_bih , thanks for asking. Table 1 is the largest table and it contains many more columns. Table 2 is quite small. Price and quantity may change over time. 

    • some_bih's avatar
      some_bih
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Hi carlenb so in which table there are "lookup" value and in which there are "fact / data"?

      • carlenb's avatar
        carlenb
        Icon for Advocate II rankAdvocate II
        2 years ago

        Hi some_bih 

         

        Your questions got me to reflect a bit 🙂 I guess to simplify and create a better schema I can add project start and number of projects to my project table instead of having four tables. Still a learner in this! So the question is the same but my updated structure looks like this: 

         

         

        Any support on how I can now calculate what I'm after?