Sum of value by day?

[Anonymous]

Hi, I am trying to calculate the sum of values by day so that I can then have a graph showing the average by month (which is based on the daily total figure). The following shows an example of the data I have and I want to have either a measure or new column that shows the daily total for EOLUsage(GB):

I can pull this into a table visualisation with no problem. However, as mentioned, I want to be able to have a graph showing average values per month and don't seem to be able to without this information already existing in a column or measure.

Any suggestions?

Thanks.

Pete.

[cosminc]

Hi,

try to use measure like this:

result =

var sumEOLU = Sum(Source[EOLUsage(GB)])

var countdays = Distinctcount(Source[Date])

RETURN
Divide(sumEOLU,countdays)

Hope it helps

Cosmin

[parry2k]

@Anonymous you need to create date dimension in your model, set relationship between your data table and date dimension. In date dimension you will have month column which you can use on x-axis and avg from data table.

There are many posts/blogs on how to create date dimension whcih you can follow to create on in your model.

[Anonymous]

@parry2k thanks. I have followed this guide to create a date dimension https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns.

I marked this as my date table in PBI and then dragged in Month as X and then EOLUsage(GB) as the data value. However, it hasn't helped as still showing the average for each value in that month rather than a sum of per day, and then an average.

Just to explain a bit more... Each day has around 700 entries each with their own values so it using all of these entries, for every day in the month to use as the average. I have only been collecting data since the 11th of July 2019 and the following shows the issue:

When I now use the Month dimension I want it to show the daily totals averaged - so in this case the figure would be 149.96 but as you can see it is a lot less than that:

Hopefully that explains my issue a bit better.

Thanks.

Pete.

[parry2k]

@Anonymous 

@Anonymous i know you explained quite a bit but don't have time to go thru all of it

so if you simply take a 5 transaction for days with sample value and explain what you want, it will help to get you the right solution. Looking at image and then trying to understand each and everythign you are explaining, takes lot of time.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@parry2k I thought I was pretty consise with my response but as suggested...

I need a sum of EOLUsage(GB) by date in a measure or column so I can reference elsewhere.

Thanks.

[parry2k]

@Anonymous oh so at month level instead 0.26 you want to see 149.xx, correct?

[Anonymous]

@parry2k correct

[parry2k · Accepted solution]

@Anonymous try following measure change table and column name as per your model

Avg Measure = 
VAR __c =
IF (ISINSCOPE( 'Calendar'[Date] ), COUNTROWS( Query1 ), CALCULATE( DISTINCTCOUNT( 'Query1'[Date] ), VALUES( 'Calendar'[Month] ) ) )
RETURN
DIVIDE( CALCULATE( SUM( 'Query1'[Amount] ) ),  __c)