Forum Discussion
Building relationships in data model to leverage power pivot - circumventing unique key ID issue
Thank you for this I'll start learning some DAX and this virtual relationship connection.
I am finding though that using that formula adds that total COGS amount ($7,500) to each Service Line row of the sales table with the same productID, and then sums it up, so I'm actually getting an aggregate of the aggregate now ($7,500 * 5 = $37,500). Is there something wrong with my formula? (couldn't find 'TREATAS' so used 'INTERSECT')
CALCULATE(SUM(COGS[COGS]),
INTERSECT(
ALL(Sales[Service Line]),
VALUES(COGS[Service Line])
)
)
I think it may have to do with the fact the sum has to be on 2 categories / criteria (Product ID and Service Line), whereas right now it's aggregating the total COGS per ProductID, and adding that to each service line (and then summing that up).