Distinct Count Multiple Columns across two tables

[clarkey1988]

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.

TMW Master Miles = SUMX(FILTER('Trip Detail','Trip Detail'[RouteNumber]=TARDbQuery[Route]&&'Trip Detail'[Month]=TARDbQuery[Month]&&'Trip Detail'[Year]=TARDbQuery[Year]),'Trip Detail'[Miles])

Please could someone help me?

[v-jingzhang · Accepted solution]

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.

[clarkey1988]