Calculate Measure - Distinct Count Average Number of Drivers Each Week

[clarkey1988]

Hi, I wonder if anyone can help. I have a table called "Pay Detail" and I want to create a measure that shows the average number of drivers we paid each week over the year. However, I have filters by different "Driver Type", "Driver Kind - Dedicated" and "Terminal".

My visual below does that for me as I have the week number in the X axis and in the value I'm simply putting distinct count of the driver ID's (each driver has a unique ID) and the result is correct. However, I need to create a measure to the side that calculates this. So the average for my terminal in Knoxville is around 62 drivers for year 2022.

Essentially, I need to find the average number of drivers for each week in the year, but it to change when I click different Driver Types, Drivers Kind or Terminals.

Can anyone assist? Thanks!

[DataInsights]

@clarkey1988,

I think you can use a simple average since your calculated table has already calculated the distinct count.

Distinct Count Weekly =
AVERAGE ( 'Driver Count - Asset'[Distinct Count] )

You may be able to avoid creating the calculated table 'Driver Count - Asset' as an intermediary step. You can create a date table with a [Year-Week] column (e.g., "2022-7"), create a relationship between the date table and 'Pay Detail' table, and then use a measure like this:

Distinct Count Weekly =
AVERAGEX (
    VALUES ( DimDate[Year-Week] ),
    CALCULATE ( DISTINCTCOUNT ( 'Pay Detail'[Driver ID] ) )
)

[clarkey1988]

Hi @DataInsights , so the issue I have with using the simple average measure from the calculated table is that the answer does not change if I click on for example a different terminal. The second screen shot answer remains the same if I click the Anderson (10) terminal and I need it to change.

The graph gives me the answer I need. For example screen shot 1 I have a hierachy slicer selected for Driver type = "Single", Driver kind = "Dedicated" and my average line number of drivers used each week in 2022 is 314 drivers. If I wanted to see this for a different terminal say "Knoxville 50" it changes to 61 and so on. It seems like I need to have many relationships from my main table to my new table I created to calculate the distinct count.

[DataInsights · Accepted solution]

@clarkey1988,

That's correct. You have to create relationships between the calculated table and the dimension tables. This isn't necessary in the alternate approach I mentioned, where you eliminate the intermediary calculated table and create a relationship between the date table and 'Pay Detail' table.

[clarkey1988]

I used your work around method and it worked perfect. Thank you so much!