Forum Discussion
Advocate IIDAX / Measure help on volume for multiple projects during different dates
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!
Advocate IIHi 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?
Community ChampionHi 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.
- some_bih
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 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 🙂