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 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!