cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

22 REPLIES 22

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.

Super User

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

Proud to be a Super User!

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?

Super User

Hi @carlenb below is possible solution for measure "Measure test". Amount of 160 for B in 2023

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)

Proud to be a Super User!

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!

Super User

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

Table 1 and project connected via project

Proud to be a Super User!

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!

Super User

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.

Proud to be a Super User!

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?

Super User

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 😕

Proud to be a Super User!

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

Super User

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.

Proud to be a Super User!

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?
Super User

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.

Proud to be a Super User!

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:

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 🙂

Super User

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

Proud to be a Super User!

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!

Super User

Hi @carlenb I really happy when I could help.

Proud to be a Super User!

Super User

Hi @carlenb  I will check it and let you know

Proud to be a Super User!

Hi @some_bih very appreciated, thanks!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.