Help With Relationship Table Where Values are Total vs Net

[Anonymous]

I am not sure what I'm looking to do can be logically done, so I am open to suggestions. Essentially, what I want to do is link up two tables with a dimension table. The dimension table has: Labor Net and Overtime Net. One of my fact tables has the net values only, so it matches up with the dimension table perfectly; however, the second fact table has Labor Base and Labor Deductions and Overtime Base and Overtime Deductions (i.e. Labor Base - Labor Deductions = Labor Net). I want to be able to build the relationship on the net values, how can I link them together? 

[PaulDBrown · Accepted solution]

@Anonymous 

From what I'm understanding, you cannot do (should not do more likely) what you are attempting to do using a dimension table (in the "traditional" sense). Dimension tables typically contain the unique values (usually text/date fields)  contained in fact tables to enable filtering in visuals, pages and measures etc.

So what you are suggesting would imply including the unique values of an aggregation from your fact tables (which in most cases makes little sense).

What you can do, is set up a table (using the "enter data" option in the home ribbon) with the values "Labor net" and "Overtime net". Let's call this table Cost Type.

This table is independent (unrelated) from your fact tables (ie, there are no relationships). You can use this table as a slicer/filter etc in your report.

You then write measures referencing the selection made on this table (as slicer etc).

For example:

Cost Type selection = IF(SELECTEDVALUE('Cost Type'[Cost Type]) = "Labor Net", [Sum of Labor Net], [Sum of Overtime Net Net])

And use this measure in your visuals:

Hope that helps. (I've attached the sample PBIX file for reference)

[amitchandak]

@Anonymous , Not very clear.

In the second table create a new column

Labor Net = Labor Base - Labor Deductions

And join with dimension table

[ToddChitt]

Not seeing the entiretly of your model so this comment is a bit of a shot in the dark, but...

Why are you linking to a dimension table on what seems to be MEASURE values? Normally links to dimension tables are done on KEY values: primary keys, surrogate keys, etc.

Not saying it can't be done, but just seems an odd way to relate tables.