Forum Discussion

Jebilaya's avatar
Jebilaya
Icon for Helper III rankHelper 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 ...
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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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.