cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Calculate Measure - Distinct Count Average Number of Drivers Each Week

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!

1 ACCEPTED SOLUTION
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
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] ) )
)``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

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.

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors