topic DAX / Measure help on volume for multiple projects during different dates in DAX Commands and Tips

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

carlenb — Mon, 25 Sep 2023 10:53:34 GMT

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.

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

some_bih — Mon, 25 Sep 2023 07:43:21 GMT

Hi carlenb table 2 is "fact" table, the table with most details?

Table 1 is just list of projects, price & quantity?

If yes, why not one table (not 2 table 1 and table 2)?

Price and quantity will be fixed, not variable during time?

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

carlenb — Mon, 25 Sep 2023 08:33:54 GMT

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.

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

some_bih — Mon, 25 Sep 2023 08:50:03 GMT

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

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

carlenb — Mon, 25 Sep 2023 10:07:04 GMT

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?

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

some_bih — Mon, 25 Sep 2023 13:04:33 GMT

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)

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

carlenb — Mon, 25 Sep 2023 14:42:35 GMT

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!

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

some_bih — Mon, 25 Sep 2023 17:18:20 GMT

Hi carlenb date is connected with start date in project table

Table 1 and project connected via project

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

carlenb — Tue, 26 Sep 2023 07:21:26 GMT

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!

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

some_bih — Tue, 26 Sep 2023 09:19:08 GMT

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.

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

carlenb — Tue, 26 Sep 2023 10:00:52 GMT

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?

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

some_bih — Tue, 26 Sep 2023 10:34:38 GMT

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 😕

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

carlenb — Tue, 26 Sep 2023 12:56:10 GMT

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

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

some_bih — Tue, 26 Sep 2023 13:03:00 GMT

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.

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

carlenb — Tue, 26 Sep 2023 13:40:05 GMT

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?

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

some_bih — Tue, 26 Sep 2023 13:51:15 GMT

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.

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

carlenb — Tue, 26 Sep 2023 14:19:02 GMT

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 🙂

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

some_bih — Tue, 26 Sep 2023 18:54:36 GMT

Hi carlenb I will check it and let you know

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

carlenb — Wed, 27 Sep 2023 05:38:34 GMT

Hi some_bih very appreciated, thanks!!

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

some_bih — Wed, 27 Sep 2023 06:10:24 GMT

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!