Forum Discussion
Helper IICalculate Measure - Distinct Count Average Number of Drivers Each Week
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.
Super User
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] ) )
)
Helper IIHi 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
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!