Forum Discussion
Bridge Tables
I think the simplest solution would be to create a calculated table that contains unique Team data, and create the necessary calculations on this table. Something like:
TEAM =
SUMMARIZE(<2nd Fact Table>, <2nd Fact Table>[TEAM], <2nd Fact Table>[DATE])
Then create a calculated columns on the new TEAM called "Amount Overtime" which calculates the sum of overtime from your first table (ERP). This way you have the sum for overtime for each team on each date.
But how would this new table relate to the point table?
If you only need to "create a chart that shows OVERTIME BY TEAM", then the new calculated table does not need to be related, and the chart can use the new calculated table as the data source. If additional charts/data points are needed, you could related the new TEAM table to your other table by the "team name" column on the original table. But depending on your other need for other charts/visualizations, additional steps may be required.
Okay, I don't know if I understand very well... Could you give me an example of how I would calculate the total overtime (taking it from the first table) since it doesn't have a team column? Would you do this by measure?
I think if you could provide some more sample information that would help. Looking back at your original question, do you need to know overtime by date and team, or just by team? Here is an example diagram of the orginal table based on the information you provided:
A straight calculation about be to calculate "TOTAL OVERTIME" on the orginal TEAM table (red text above), but this would not take into account the "DATE" because you have no date field on ERP. If you don't need to know overtime by date, then you could calculate a table this way:
Create the summarized table with just "ID", "NAME", "TEAM", then create a calculated column to sum overtime.