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

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

Hi some_bih thanks for the patience with me 🙂 

 

What I did was to create a test dimension table for Project, containing only 1 project. I got this relationship to work, RELATED works and the calculations works as well. 

 

The problem is that in reality there will not be unique values in the project table. Maybe I was bad at explaining this in the beginning, sorry for this in that case. In reality the project table looks liks this: 

 

 

So for this reason I can't avoid having duplicates in the project table and 1:1 relationship will not work. 

 

Does this make the case more clear and how would you solve it?