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