Forum Discussion

Jebilaya's avatar
Jebilaya
Helper III
4 years ago
Solved

Structure setup suggestions - values across two columns

I have a table that contains delivery data which has a driver and a porter against it.  I need to run some performance data by driver / porter.  The problem is that a driver may be both a driver and porter on different days so to get their total jobs across multiple days I need to count across the two days.

 

How am I best to approach this?  I thought about creating a copy of the table that only has the porter names and then making a new table that basically pulls in duplicates of each row but then with the driver name on row 1 and then porter name in the same column on row 2.  Or is there a way I can write measures to sum totals across the two columns based on the name?

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Jebilaya,

     

    I would approach this by creating a star schema consisting of a dimension table with one row for each Driver/Porter (this table will contain unique names; if your data has an Employee ID or equivalent, use this column to ensure uniqueness). Then, create two relationships between the dimension table and fact table; one relationship will use the fact table Driver column, and the other relationship will use the fact table Porter column. It doesn't matter which relationship is active (one is active, one is inactive). In this example, the Driver column has the active relationship.

     

    Create the following measures:

     

    Driver Total =
SUM ( FactTable[AmountColumn] )

    Porter Total =
CALCULATE (
    SUM ( FactTable[AmountColumn] ),
    USERELATIONSHIP ( DimensionTable[Name], FactTable[Porter] )
)

     

    In a visual, use the dimension table Name column.

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Jebilaya,

     

    I would approach this by creating a star schema consisting of a dimension table with one row for each Driver/Porter (this table will contain unique names; if your data has an Employee ID or equivalent, use this column to ensure uniqueness). Then, create two relationships between the dimension table and fact table; one relationship will use the fact table Driver column, and the other relationship will use the fact table Porter column. It doesn't matter which relationship is active (one is active, one is inactive). In this example, the Driver column has the active relationship.

     

    Create the following measures:

     

    Driver Total =
SUM ( FactTable[AmountColumn] )

    Porter Total =
CALCULATE (
    SUM ( FactTable[AmountColumn] ),
    USERELATIONSHIP ( DimensionTable[Name], FactTable[Porter] )
)

     

    In a visual, use the dimension table Name column.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Jebilaya ,

    According to your description, I create a sample.

    In my understanding, you want to calculate the sum of amount for each worker, whether he is a driver or a porter. If this is the case, here's my solution.

    1.Create a new table including all drivers and porters.

    Work Table = DISTINCT(UNION(VALUES('Table'[Driver]),VALUES('Table'[Porter])))

    2.Create a measure.

    Amount =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Driver] = MAX ( 'Work Table'[Worker] )
            || 'Table'[Porter] = MAX ( 'Work Table'[Worker] )
    )
)

    Get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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