Forum Discussion
Sum of Values, comparing two fields from different tables
I have 2 Tables(Steps and Entries).
StepNo EntryNo, StepNoInitial, StepNoFinal, Amount
Step1 1 , Step1 , Step3 , 20
Step 2 2 ,Step1 , Step3 , 7
Step 3 3 ,Step2 , Step1 , 15
i am trying to calculate the total sum amount for every stepno combination. i did a cross join on the Steps table to give me the all possible combinations of the Steps Values,
StepsCrossJoin = CROSSJOIN(SELECTCOLUMNS(Steps,"StepNoInitial",Steps[StepNo]),SELECTCOLUMNS(Steps,"StepNoFinal",Steps[StepNo]))
)Now i need a measure to be to calculate the sum of amount from the Entries table on the condition below
StepsCrossJoin[StepNoInitial]=Entries[StepNoInitial] && StepsCrossJoin[StepNoFinal]=Entries[StepNoFinal]
I need help to go about writting the measure, any help is appreciated
2 Replies
- amitchandak
Super User
Gottijay2000 , if first table is like a master table, then you can join it with both steps fields and one join will be inactive. You can activate join in a measure using userelationship
Hi amitchandak
the Steps table has unique values and Joins the Entries in a 1-Many relationship, but does not hold all the values the conditions require for the measure.
The StepsCrossJoin table is what i need to use as the main table as it holds the values the conditions will be run against. Only issue here is that it is a Many-2-Many relationship with the Entries Table.i'm confused as how to make this work, where i can filter the Entries table for where the StepNoInital field = CrossJoin[StepNoInitialField] && StepNoFinal field = CrossJoin[StepNoFinal] field and then sum the amount.
