How to get average of a measure by date excluding 0's, (or blanks)?

[Anonymous]

Hi,

I can't share my data, but i will try to explain it. So I have observations made by a creator, they go out and count observations in the field. So creator is never blank in the dataset, there is always a creator who made the observation. Then the other value I'm working with is by date, there is always a date recorded for when the creator made the observation. Then I made a measure called "Submissions) which is just a count function of each observation. So I've been making tables of submissions by creator and slicing them by date for example, so I can see how many observations each creator made on a certain date.

Now, what I'm trying to do is get the average of submissions by by creator per date. I am trying to make a table that has creator as the x axis, so I can display the average for each creator, and this measure as the y axis. 

I tried this coding, but the issue that I'm running into is that the averages are skewed, so that creators with more total observations over the long haul have much higher figures than creators who have not made as many observations overall. However, I only want the measure to be calculated for the dates that a creator made any observations at all, so basically if the observations on that date where greator than 0. Any help would be greatly appreciated.

Thanks,

Kelly

CountRows =

CALCULATE (

COUNTROWS ( VALUES ( SubwayCount_Export_210210[Collection Date] ) ),

FILTER( VALUES ( SubwayCount_Export_210210[Creator] ), SubwayCount_Export_210210[Submissions] <> BLANK())

)

[sturlaws]

Hi, @Anonymous,

in insituations where you can't share or show any data, it is usually a good idea to create a mockup dataset/report which resembles your data. If you provide sample data, your questions will be very quickly replied.

For you problem here I have created a small mockup report:
mockup report 

with the measure written like this:

average number of obs =
DIVIDE (
    SUM ( 'Table'[Number of obs] ),
    CALCULATE (
        COUNT ( 'Table'[Observation date] ),
        FILTER ( 'Table', 'Table'[Number of obs] > 0 )
    )
)

cheers,

Sturla

[Anonymous]

Hi,

Thanks so much for this, seems to be on the right track. However, the number of observations is itself a measure, so I cannot insert it into the sum function. Would I use values maybe to get the sum by date?

Thanks,

Kelly

[sturlaws · Accepted solution]

you can replace SUM ( 'Table'[Number of obs] ) with any already existing measure