Forum Discussion

PBIUWO's avatar
PBIUWO
Helper III
7 years ago
Solved

Count rows in relation to another column?

I have 1 table that has the column, Order Number, Item Code, and Shipping ID. 

 

I am trying to get the count of unique Shipping ID to the Order Number. 

The data:

 

Hoping to create a column that result in the below, and to find the average. 

 

 

Currently, I have the calculation 

COUNT OF SHIPPING ID PER ORDER = CALCULATE(DISTINCTCOUNT('Table 1'[Shipping ID]),'Table 1'[Order Number]) and COUNT OF SHIPPING ID PER ORDER = CALCULATE(COUNTAX(
Table 1', 'Table 1'[Shipping ID]),'Table 1'[Order Number])

but both result in 1 Count for all the lines. 

 

Thank you, 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi PBIUWO ,

     

    To create relationship between tables as below.

     

     

    Then we can create a calcualted column to get the result we need.

    count of shipping id = 
CALCULATE ( DISTINCTCOUNT ( data[Shipping ID] ), RELATEDTABLE ( data ) )

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi PBIUWO ,

     

    To create relationship between tables as below.

     

     

    Then we can create a calcualted column to get the result we need.

    count of shipping id = 
CALCULATE ( DISTINCTCOUNT ( data[Shipping ID] ), RELATEDTABLE ( data ) )