Divide Function with Filter Across Multiple Linked Tables

[Anonymous]

Hi Everyone, 

Thank you in advance for your time and input, this problem has been plaguing me for a while now. Currently I have the data below (summarized), within two different tables, these tables are linked via the ToolID columns: 

Table 1:

StationID	ToolID	Moves
aaa1	xxx1	10
aaa1	xxx2	15
aaa2	xxx3	20

Table 2:

ToolID	Cost
xxx1	20
xxx2	30
xxx3	40

What I have tried doing is using the functions calculate/filter/sum/divide/crossfilter to solve this problem:

I want to sum the tool ID cost by Station ID (for example: StationID "aaa1" Cost = 20 +30 = 50

Then I would use the divide function to have total cost by station family / total move:

Measure Column withhin the matrix visual would show: 

StationID	Cost/Move
aaa1	50/25
aaa2	40/20

Thank you again! Truly appreciate the support/time. 

[fhill · Accepted solution]

Assuming ToolID is in a defined relationship between the two tables, I was able to do this Measure below on Table 1.  Hope it helps, Forrest

[Anonymous]

Question, how does this function filter to ensure that the cost by ToolID is related to a StationID. How is it linking ToolID's xxx1 and xxx2 to StationID aaa1? Within my real data set there are thousands of costs per ToolID as well as thousands of moves per ToolID which is already linked to StationID within the Data Flow. 

[fhill]

Since I created the Meausre on Table 1 (Station ID, ToolID, and Moves) it reads that table as the limiting factor.  "For every Station ID, we have X ToolIDs.  Now ToolID matches THESE other ToolIDs on this other table, so go get ONLY their mathcing Costs."  Hope this helps... Forrest

[Anonymous]

Okay that is awesome, I was thinking about it further and this makes perfect sense, I overthought this with so many filter functions and declared variables. It will work similarly if I wanted to calculate the CPM for Tool ID as well? So for reference, CPM (xxx1) = 20/10 [using your models values]. Appreciate your support and help here, truly. 

[fhill]

It should.  If your Relationship is a 1 to Many, just build the CPM on whichever table is the "1" so it pulls all the "Many"s from the other table...

[Anonymous]

I'm not sure if this will work sadly then because each ToolID has hundreds of costs associated with it and each tool id has hundreds of moves as well. There is no 1 to many relationship, it is a many to many. 

[fhill]

This does explain why you were having to try the more complicated filters, can you post a few real rows of sample data?  Can a ToolID be on multiple different StationIDs also?

[Anonymous]

I tried posting a more thourough data set and the site pinged me with an error, each toolID is only associated with 1 StationID. The Moves table contains information of a ToolID, StationID and Moves level whereeas the Cost table contains information only relating ToolID to cost. The function I am trying to create will accomplish 2 tasks: 

1) sum total cost from cost table by ToolID / sum total moves by toolID 

2) sum total cost from cost table by ToolID filtered to StationID / sum total move by StationID

The Cost and Move tables are linked through the ToolID with a Many to Many and Both cross filter direction. 

[fhill]

If you can (if your tables aren't already in complex relationships, try going to Modeling - New Table and using this code to create a true Unique Distinct ToolID table.  It can then be joined 1 to Many to both your existing tables, and your Measures can be palced here to do the math you need...

Unique_ToolID = DISTINCT( UNION( DISTINCT('Table'[ToolID]), DISTINCT('Table (2)'[ToolID])))

From here, we may need to understand your tables and data more accuratly to assist furhter, see if you can copy and paste some sample data into a new ticket, so it won't show resolved.  Tag me in your post @fhill  and i'll try to jump in.

[Anonymous]

*I forgot to add that it would probably be two different measures* 

[amitchandak]

@Anonymous , Create a new table 

Dim =summarize(Table, table[StationID],table[ToolID])

join with both tables on ToolID

and then analyze with columns of dim

new measure = divide(sum(Table2[Cost]),sum(Table[Move]))

[Anonymous]

I looked into the summarize function, doesnt it work by summarizing columns from within 1 table? I would want to join 2 different tables linked by ToolID.