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 dat...
  • 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!

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

Hi carlenb now when we have Project table, what other tables / data you have in your model and what you want to be calculated? 

Today we are at start ğŸ˜•

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

Hi some_bih, let me try to clarify, again thanks for your patience 🙂 What's needed is to calculate the total sales volume for each project, and then again multiply this against the number of projects. Projects will start at different starting dates. So the end result will be a graph where you can filter on different projects or dates/years and see the total value for that. 

 

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

Table 1 looks like this 

So table 1 will be unique in project name and sub project name. I will add more tables like table 1 over time, one table for each individual project. In the picture I have the project "CTV PPC" as an example. But I will create more tables for the projects "CTV KGH", "ABC YUL", "ABC TUI" etc. as seen in table 2. 

 

Table 2 looks like this