Average gives incorrect number Power BI Desktop

Question

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

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?

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.

What value are you expecting? Are you looking for the average number per day? — Alexis Olson, Feb 09 '18 at 17:19
Oh, is it what is does? Im expecting average per selected data range — Serdia, Feb 09 '18 at 17:20
Average over what though? Per row, per day? What would you consider the "correct" value to be? — Alexis Olson, Feb 09 '18 at 17:24
Yes, I think its per day, I updated question. Sorry for confusion — Serdia, Feb 09 '18 at 17:48
So writing measure `AVG_OpenTasks per Day = DIVIDE(SUM(TotalCounts[OpenTasks]),COUNT(DimDate[Date]))` should do the trick. — Serdia, Feb 09 '18 at 17:52
That measure looks reasonable to me, but I think you want the number of days in the denominator in your post rather than tasks per day. — Alexis Olson, Feb 09 '18 at 17:57
Oh yes. That's correct. Thank you for pointing me to the right direction. I'd love to accept your answer. — Serdia, Feb 09 '18 at 18:06

score 2 · Accepted Answer · answered Feb 09 '18 at 18:09

2

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.

answered Feb 09 '18 at 18:09

Alexis Olson

38,724
7
42
64

score 1 · Answer 2 · answered Feb 09 '18 at 21:39

1

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

answered Feb 09 '18 at 21:39

Zubda

943
1
6
16

To learn more about DAX I would recommend Marco Russo and Alberto Ferrari, they are (in my opinion) the best in DAX, there are 2 great videos on YouTube from them; 1. DAX 101 2. DAX 50 – Zubda Feb 09 '18 at 21:43
thank you for learning recommendation. Will definitely look at it. – Serdia Feb 09 '18 at 23:50

score 0 · Answer 3 · answered Jun 20 '19 at 19:29

0

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.

answered Jun 20 '19 at 19:29

user11677933

1

Average gives incorrect number Power BI Desktop

3 Answers3

Linked