Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
@Anonymous oh so at month level instead 0.26 you want to see 149.xx, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |