Average gives incorrect number Power BI Desktop

0
votes

I have two columns with OpenTasks and ClosedTasks, both have values either 0 or 1.

enter image description here

Then I use matrix grouped by date and want to get an average for each column. But number seems incorrect to me. 0.44 and 0.56 I tried to create measure, but gives me the same result.

What am I missing?

enter image description here

UPDATE: On a picture below I'd expect Sum(TotalTasks)/ (Number of Total Tasks per day) which is: 2,818 / 10 = 281 Is that would be average, am I right? Sorry I'm confused.

enter image description here

3
powerbidaxpowerbi-datasource
What value are you expecting? Are you looking for the average number per day? – Alexis Olson
Oh, is it what is does? Im expecting average per selected data range – Serdia
Average over what though? Per row, per day? What would you consider the "correct" value to be? – Alexis Olson
Yes, I think its per day, I updated question. Sorry for confusion – Serdia
So writing measure AVG_OpenTasks per Day = DIVIDE(SUM(TotalCounts[OpenTasks]),COUNT(DimDate[Date])) should do the trick. – Serdia

3 Answers

2
votes

It looks to me like the measure is averaging the task per row level. I believe you want to average them at the day level. Take the sum of tasks and divide by number days to get the average count per day.

1
votes

The use of the AVERAGE function runs in the whole column visible in the current filter context, meaning that it returns the average based on each row, you need to use an iterator function like AVERAGEX to get your desired result

0
votes

I found that if I went into my data set and created a column Called KPI Trend and just made it = 1, then use that as my Trend axis I got expected results for my averages.