Forum Discussion
Dividing two columns
Hi All,
I was wondering how I can create a measure that divides Table 2 by Table 1.
Specifically I want to be able to divide Table 2 by Table 1 by date.
The 'quantity' in Table 2 would be divided by the 'quantity' in Table 1 for each corresponding day.
Please let me know what other information I can add.
Hopefully the visual below helps
Thanks!
4 Replies
The column way
New column in Table 2 = divide(table2[quantity],maxx(filter(table1,table1[month] = table2[month]
&& table1,table1[day] = table2[day]
&& table1,table1[the line] = table2[the line]),table1[quantity]))For measure you need have common date and line dimension
Thanks amitchandak that worked out great!
For some reason I couldn't type your code exactly but I got it to work.
Here's what I tried for the new column using tables 1 and 2.
You'll see the exact names of Tables 1 and 2 below.
New Col T2 using T1 =DIVIDE(dailyBadData[quantity], MAXX(FILTER(dailyGoodData,dailyGoodData[theDate] = dailyBadData[theDate]&& dailyGoodData[theDate].[Day] = dailyBadData[theDate].[Day]&& dailyGoodData[theLine] = dailyBadData[theLine]), dailyGoodData[quantity]))I also tried this formula with another table, Table 3, but I didn't get a different answer.
Here is what I used to the Table 3 column
New Col T2 using T3 =DIVIDE(dailyBadData[quantity], MAXX(FILTER(testLineData,testLineData[theDate] = dailyBadData[theDate]&& testLineData[theDate].[Day] = dailyBadData[theDate].[Day]&& testLineData[theLine] = dailyBadData[theLine]), testLineData[quantity]))Any clue as to what I'm doing wrong?Again thanks for answering the first post.
