Forum Discussion
Distinct Count Multiple Columns across two tables
Hi,
I have a calculated column in Table 1 (TARDbQuery) that sums the total miles for an order number from Table 2 "Trip Detail" based off the month and year in Table 1.
So I have a total of 6,190 miles for an order number in January 2020.
I now need to divide this by the disincty count of order numbers in Table 2 using the same parameters as above.
Please could someone help me?
Hi clarkey1988
Which parameters do "the same parameters as above" refer to? If they are Month and Year, you could try below codes to add a calculated column in Table 1.
Column = DIVIDE('Table1'[TMW Master Miles],CALCULATE(DISTINCTCOUNT('Table2'[RouteNumber]),FILTER('Table2','Table2'[Month]='Table1'[Month]&&'Table2'[Year]='Table1'[Year])))If they are RouteNumber, Month and Year, I think the distinct count is always 1 (assume that Route Number is order number)?
If I don't understand it correctly, can you share some sample data and expected result without sensitive info so we can work with it further?
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
2 Replies
Hi clarkey1988
Which parameters do "the same parameters as above" refer to? If they are Month and Year, you could try below codes to add a calculated column in Table 1.
Column = DIVIDE('Table1'[TMW Master Miles],CALCULATE(DISTINCTCOUNT('Table2'[RouteNumber]),FILTER('Table2','Table2'[Month]='Table1'[Month]&&'Table2'[Year]='Table1'[Year])))If they are RouteNumber, Month and Year, I think the distinct count is always 1 (assume that Route Number is order number)?
If I don't understand it correctly, can you share some sample data and expected result without sensitive info so we can work with it further?
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
