Forum Discussion
Building relationships in data model to leverage power pivot - circumventing unique key ID issue
In general it's not necessary to build bridge table to handle many-to-many relationships, as variant you may use virtual relationships. based on your sample let assume we have two tables
For them we to calculate COGS we may use measure
COGSv:=CALCULATE( SUM(COGS[COGS] ),
TREATAS(
VALUES( Sales[Service Line] ),
COGS[Service Line]
)
)which gives
Compare with
Total COGS:=SUM( COGS[COGS] )More details at Physical and Virtual Relationships in DAX - SQLBI and you may google for other sources.
Thank you for the response, I'll look into learning some DAX and virtual relationships.
I'm find with that formula, it's aggregating the aggregate ($7,500 * 5 = $35,000) instead of providing the COGS per service Line per Product ID (as you have shown above under COGSv). I couldn't find TREATAS so used INTERSECT.
I tried creating another formula, which also produces the same result. Is there a formula (other than TREATAS since I can't seem to find/ use it) which can summarize COGS per Product ID and Service Line (rather than summing COGS per Product ID, and applying that to each Service Line row, and then aggregating that - which is what is currently being done).
This is my current formula:
=CALCULATE(
SUM(COGS[COGS]),
INTERSECT(
SELECTCOLUMNS(
SalesTable,"Criteria1",SalesTable[Service Line],"Criteria2",SalesTable[ProductID]
),
SELECTCOLUMNS(
COGSTable,"Criteria1",COGSTable[Service Line],"Criteria2",COGSTable[ProductID]
)
)
)
I don't remember when TREATAS was introduced in Excel, relatively long ago. However, it's still highlighted by red (as not recognized) in formulae. But it works if to enter it. Usually TREATAS has better performance and easier to use, at least for me.