FInd unique places visited per transport

[Anonymous]

Hello,

Our company has trucks that have to visit certain of our storage places to fill up their cargo. Sometimes all of their cargo is in the same storage place, sometimes they have to drive around to fill up. We get the following data. Every row represents a part of the cargo loaded in the truck and the place visited for that part of the cargo:

TRANSPORT IDENTIFIER      STORAGE PLACE VISITED

31xv                                     A

31xv                                     A

31xv                                     A

45sf                                      B

45sf                                      B

45sf                                      C

z7re                                      A

z7re                                      B

z7re                                      C

I want to calculate the average of unique storage places visited by a transport. So in this case. The first transport, 31xv, only visited storage place A, so that's one unique place visited. The second transport, 45sf, visited storage room B and C, so two unique storage places visited. The third transport, z7re, visited A, B and C, so that's three unique places visited. So we have 3 transports, that visited 1, 2 and 3 unique places respectively. This is 6 total. 6/3 = 2, so each transport has to visit on average 2 storage places. Can somebody tell me how to calculate this in a measure? My collegue and I were trying with CALCULATE and DISTINCTCOUNT but couldn't seem for find the right result. Thanks in advance.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please check the measure below.

Measure =
SUMX (
    ALLSELECTED ( 'Table'[TRANSPORT IDENTIFIER] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[STORAGE PLACE VISITED] ),
        ALLEXCEPT ( 'Table', 'Table'[TRANSPORT IDENTIFIER] )
    )
)
    / CALCULATE ( DISTINCTCOUNT ( 'Table'[TRANSPORT IDENTIFIER] ), ALL ( 'Table' ) )

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @Anonymous ,

Try this measure?

CNT =

VAR _tbl = SUMMARIZE('Table'; 'Table'[TRANSPORT IDENTIFIER]; "CNT"; DISTINCTCOUNT('Table'[STORAGE PLACE VISITED]))

RETURN IF(HASONEVALUE('Table'[TRANSPORT IDENTIFIER]); SUMX(_tbl; [CNT]); DIVIDE(SUMX(_tbl; [CNT]); CALCULATE(DISTINCTCOUNT('Table'[TRANSPORT IDENTIFIER]); ALL('Table')); 0))

It will change the calculation for the total.

Ricardo