DAX / Measure help on volume for multiple projects during different dates

[carlenb]

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 date for projects, column D = number of projects
• Calender table

I need to calculate the total volume (B*C) for each project (table 1), and then multiply this with the number of projects (project table, column I). A project can be divided into unique sub projects. Each project will start at different times, so there might be 2 project starts in November 2023, 5 at January 2024 etc. So in summary - for each project start, I need to total value of that project.

See below as an example. I would really appreciate help to create a measure in DAX on this.

[carlenb]

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]

Hi @carlenb so in which table there are "lookup" value and in which there are "fact / data"?

[carlenb]

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]

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]

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]

Hi @carlenb date is connected with start date in project table

Table 1 and project connected via project

[carlenb]

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]

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]

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? 

[some_bih]

Hi @carlenb now when we have Project table, what other tables / data you have in your model and what you want to be calculated? 

Today we are at start 😕

[carlenb]

Hi @some_bih, let me try to clarify, again thanks for your patience 🙂 What's needed is to calculate the total sales volume for each project, and then again multiply this against the number of projects. Projects will start at different starting dates. So the end result will be a graph where you can filter on different projects or dates/years and see the total value for that. 

• Table 1: Column A = Project name, B = Sub project name, C = Article number, D = Price, column E= Quantity
• Table 2 with column A = Project number, B = Project name, C = Sub project name, D = number of projects E= start date for projects
• Calender table

Table 1 looks like this 

So table 1 will be unique in project name and sub project name. I will add more tables like table 1 over time, one table for each individual project. In the picture I have the project "CTV PPC" as an example. But I will create more tables for the projects "CTV KGH", "ABC YUL", "ABC TUI" etc. as seen in table 2. 

Table 2 looks like this

[some_bih]

Hi @carlenb I would ask you to think again about your model / example you provide.

Reasoning:

Example should reflect your REAL actual data, possible combination - for sure you can hide sensitive data, and details but actual data should be provided to model / provide solution. 

In your example, in table 2 there are many projects comparing with table 1, this cause relationship creation and possible solution.

I really tried to understand your data and provide solution, but you did not provide sound example with output.

Please, put your actual data and process in tables, describe and replace it with some names to hide sensitive data... Maybe this is hard / new to you but will benefit you in long run.

Hope you understand.

[carlenb]

Hi @some_bih, thanks for getting back. 

I'm sorry but I don't understand what you need. Let me explain:

• The example I have provided is exactly the tables I have, I have only swapped the real data to dummy data. The only difference from real life is that I have excluded columns not relevant at all for the calculation such as currency, unit columns etc.
• The RELATED solution you have provided above do in fact work when there are unique rows (project names) in table 2, I tried this and I get the expected output value for that project. But in my data this is not the case because for each row in Table 2 there will be a project name and a project start. So each project name will occur multiple times and not a single time. This must also match on the sub-category project. Hence my understanding is that I can only create a many to many relationship, which is causing RELATED not to work. 
• Based on this and the data I actually have in front of me: what should I do?  

[some_bih]

Hi @carlenb 

Take a look closely at least two issues with current example. I would say, granularity of data in your example should be adjusted either change table 1 or table 2.

[carlenb]

Hi @some_bih you are going to be my future hero after all this help 🙂 

It is fine to retrieve on an overall level and include all articles. So for clarity the output for CTV PPC would be: 

• Value for CTV PPC will be Price * Quantity = 200 (Retrieved from Table 1) 
• Total value for CTV PPC in 2024-01-01 will be 200 * 2 = 400 (Table 2) 
• Total value for CTV PPC in 2024-02-04 will be 200 * 2 = 400 (Table 2) 

If it makes it easier to see the data I also created a duplicate of the model, you can see it here: 

https://drive.google.com/file/d/1xVMSn-auYJJVGX2s26YjHeDK51hCNYT6/view?usp=drive_link

In this case I also added another project so that you can see how it works for more data. 

Still learning, I understand sharing the model earlier would've been good 🙂 

[some_bih · Accepted solution]

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]

Hi @some_bih 

Very thankful for all your support and help on this. I got it to work thanks to all of your patience! You're officially my hero 🙂 

Thanks a lot! 

[some_bih]

Hi @carlenb I really happy when I could help.

[some_bih]

Hi @carlenb  I will check it and let you know

[carlenb]

Hi @some_bih very appreciated, thanks!!