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

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

        Hi carlenb this issue was reason for my question, what is fact what is dimension tablee (table 1 and project)

        Put all projects in one tabl table which should be dimension, other should be fact and change type of relationship. This is best practice. Many to many is advance topic, not suitable currently.