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!

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?

       

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

        Hi carlenb below is possible solution for measure "Measure test". Amount of 160 for B in 2023

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

        Measure test =
        SUMX(Table1,
            Table1[Price]*Table1[Quantity]*RELATED('Project Table'[Number of projects])
        )
         
        Relatinships (caution: in example there are values A-C presented in both tables: Table1 and Project Table; in your model this could be different and affect results)