Calculating a lifetime average and normal distribution


#1

Halo Sam

I did ask this, but I suspect in the wrong place. So what I would like to do is plot data points over date around its average and standard deviation, here is the data;

So far I have tried calculating the average using,

CALCULATE(Average([Column]), FILTER(Table, ALLEXCEPT(Date table[date column]) )

If I plot this over dates on a line graph it keeps on calculating the average for every single date value on the X-axis. It seems power bi sums the tonnes for date value on the x-axis, divided by the amount of date values, which is just one, meaning giving me the tonnes value for that day. I cant seem to calculate a sort of lifetime average for day, week, month, …ect.
If you could please help with this?

Then I would also like to plot a normal distribution for this over the following data intervals

The second column indicates the amount of days for which my data values(highlighted column in 1st pic) falls within the relevant interval.

Any help will be greatly appreciated.

Thank you


#2

Just a little bit more on the average, the average and standard deviation should be for the FY, but the graph must be plotted over days since I want to show the daily data points around this FY average and std.


#3

Let’s start with the average.

Not sure why using this formula.

You should be able to just use AVERAGEX I would have thought.

Something like this

AVERAGEX( Table, Column)

Then is the daily context it will just work out the average for each day.


#4

Halo Sam

Thank you for the reply. I’ve tried AVERAGEX, but the problem is, because there is only one data point per day, the average per day is just again that particular amount for that day which just gives me the tonnes per day.

Here is an example of what I would like to do;

I don’t know if you will be able to see it, but there is light blue line which is the average for the financial year and the two red lines are obviously the standard deviation with then the tonnes for each day plotted inside and around the standard deviation and average respectively.

Now I think AVERAGEX in a calculated column can work, but then I assume its going to be (Total tonnes for whole table)/(count(data points)) and I need to be able to get the average for just the FY.

Maybe I am using the function wrong?


#5

Maybe I am using the wrong visualisation, is there a specific graph in power bi one can use for this?


#6

Ok I’m a bit confused then, as you described you wanted to find the average of this column and then place it in a line chart with a daily context. So you’ll have to give me more detail around the ‘average’ that you actually need.

I don’t recommend placing an AVERAGEX in a calculated column. You won’t need to. But I would have to understand what average you mean. Is it a moving average, and rolling average, monthly average, weekly average. Understanding which one is very important as the formulas will all be different

One recommendation with these (and just generally when learning DAX) is to look at the numbers versus the visualisations. When you see the numbers you can get a much better grasp as to what a formula is actually doing and it’s behaviour in a certain context.

Chrs


#7

Do you actually just want to average across every single day?

That would be this formula

AVERAGEX( ALL( Table ), Column )


#8

Ok I see

In this context it would be a moving average in FY now that I think about it, meaning I want to calculate the average tonnes per FY so it will change from FY to FY. The result should be a flat average per FY, so when I plot the tonnes per day over DATE(days), I want to see a straight line which is the average tonnes for that particular FY with the daily tonnes plotted over around the FY average.


#9

The straight line I am referring to would be a simple Average = (Total tonnes for FY)/Days per FY. A simple example would be; lets assume my tonnes for the current FY so far is 10000 with 4 days into the financial year, my average tonnes/day for this FY would be, 10000/4 = 2500. Assuming the tonnes for the 4 days was as follow, day 1 = 2500, day 2 = 5000, day3 = 500 and day 4 = 2000. When I plot this on a line chart over days, I would like to see a straight line which is 2500 for every day, with my tonnes for every day plotted around this line.

It feels like I am making this a lot more complicated than it should be due to my lack of knowledge on dax.

Ok so I found a way to do it, quite a work around, but alas it worked.

Obviously a measure would be appreciated, but for now it worked.

Do you have any idea how one would do a normal distribution based on the intervals I posted earlier?


#10

Ok sure, so if you wanted to calculation average per financial year you could use a measure formula like this.

CALCULATE( Average( tonnes column), ALL( Dates), VALUES( Financial year column from date table) )

That is one way to get that calculation.

Regarding the standard deviation, I haven’t worked with this too often but there is both PERCENTILE.X and also a range of STDEV functions.

I would have a look through all of these function first to see which one you think will give you what you need as they all have slightly different nuances to them.

Then check out this tutorial as a bit of a primer on how to structure statistical analysis type formulas

The standard deviation technique will be similar to this example, just using a different function

Chrs


#11

Thank you Sam, the measure worked. I will watch the video, thank you again.


#12

That’s great.