Calculate the average of a distinct count measure

[Anonymous]

Hi,

Can someone please help me with the syntax to find the average of a measure using DISTINCTCOUNT. Here is the syntax of my measure:

Number of Units = DISTINCTCOUNT( LoggedInData[Unit_ID] )

I was hoping I could just do something like this: Avg Number of Units = AVERAGE(DISTINCTCOUNT(LoggedInData[Unit_ID])) but that doesn't work as I am not able to reference my measure.

My Number of Units measure counts the number of times a unique Unit_ID appears in my LoggedInData table.

Thanks!

[Anonymous]

Hi @Anonymous,

For this scenario, I'd like to suggest create a variable summarize table with filter and add distinct count as a new column to summarize table. Then you can simply calculate average result based on summarize variable.

If you confused on coding formula, please share some sample data. (upload a pbix file or excel file with sample data to onedrive or google drive, we can down these files to test)

Regards,

Xiaoxin Sheng

[LivioLanzo]

Hi @Anonymous

what is the basis for the average calculation? monthly? yearly? 

[Anonymous]

Here is a screenshot of what my matrix looks like.  It is by 15 minute interval.  Where the values are now those are the distinct counts but I need them to be averages.

[LivioLanzo]

Average of what Exactly? Because I assume you do not want to average an ID?

[Anonymous]

I need the average number of units (vehicles) that were logged on.  I need to take my count of the number of units (vehicles) that were logged on and apply an average calculation to it.

[LivioLanzo]

hi @Anonymous

Lets say you are counting the number of vehicles on a Monday between 5 p.m and 5:15 pm

The number of vehicles is 100.  If you have just one number then the average of 100 is 100, unless you are counting the number of active vehicles at 5:00 pm, at 5:05 pm and at 5:10 pm and then calculating the average between 5 and 5:15

Anyway, perhaps if you share your data it will be easier to see what you are after!