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 

 

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)

 

 

 

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

    Thanks a lot some_bih 

     

    I'm getting an error that the column 'Project table'[Number of projects] doesn't exist or it doesn't have a relation. I'm trying to set up the correct relationships between the tables, can you elaborate on which relationships you set up in your data model? 

     

    If you have any alternative solutions as a work-around, that would also be extremely appreciated! 

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

      Hi carlenb date is connected with start date in project table

      Table 1 and project connected via project

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

        Thanks some_bih 

         

        I'm still struggling. I guess one reason may be that I can't create a 1:1 relationship between Table 1 and project, it forces me to create a many to many relationship. 

         

        Is there any other DAX I could use as a work-around to RELATED? Any help would be very appreciated!