Hi Guys, I want the average from a time Period.In one Table i have 2 Rows like this: Date: Values: 01.01.2016 10001.01.2016 5001.01.2016 1001.02.2016 5001.02.2016 5001.02.2016 20 If i creat visuals with average i only get the full average of all data ( 46,6666) but i need it per Month 'average 01/2016 = 53,33 02/2016 = 40I tryed a lot of things with the little DAX i know but nothing worked out correctly. thank you in advance

Hi Mertsch, I have tested it on my local environment, you can add calculated column to display the month name using the DAX belowMonth = MONTH(MonthAverage[Date]) And then create a measure MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month]) Regards, Charlie Liao

Calculate an average from a Sum by Month | Microsoft Fabric Community

16 Replies

mertsch
Frequent Visitor
10 years ago
edit: sorry i explane it wrong....

what i need is:

the final average:

01/2016 = Sum 160
02/2016 = Sum 120

average: 140
- nikil
  Resolver I
  10 years ago
  Looks like you want to get the average by Summing the amount and dividing it by the number of months/distinct dates rather than dividing by the number of rows.
  This might help, write following DAX measures:
  Amount=SUM([Values])
  NumOfDistinctDates=DISTINCTCOUNT([Date])
  Avg=DIVIDE([Amount],[NumOfDistinctDates])
  
  nikil
  Check out the Chicagoland Power BI User Group
  - mertsch
    Frequent Visitor
    10 years ago
    well it wokrs for the full tabel but if i used filters it dont work for it.
    
    ( 140 is correct for all data )
    
    But after using Filters it will be still 140. it need to be 60 in that case
    
    There must be a other way to have it more flexible with using filters
    The data need to be calculated acording to the used filters and visual in front end.
    
    May it works with GROUPBY Month Dates?! But i dont get the DAX work
    = GROUPBY (Tabelle1;Tabelle1[Month];“TEST2”;SUM(CURRENTGROUP();Tabelle1[Total]))
- Greg_Deckler
  Community Champion
  10 years ago
  Try creating a calculated column like:
  
  Month = MONTH([Date])
  Then add your Average measure and Month to a table.
MartaRamos
Frequent Visitor
8 years ago
I have the quantity of employee per month, need to calculate the average per month
Final Qty Average per month
January :100 100
February: 120 110
March: 90 105
April :: 100 95

Average= (Qty 1+Qty)/2
i am struggling with this , if someone can help me, :) :(
Clinical_Epi
Advocate III
3 years ago
Hi All, (funny I came here for an average per month of a sum - which is elluding me)...

However I deal with dates and odd data a lot, supprised you guys are having an issue...
So a percentage is numerator/denominator (then click on the % in your column tools) easy enough... but over time can be an issue. So lets say you create a simple % measure, but wan tot look at it over time. (say Month to date, last month and year to date).
1) in your hospital data duplicate your date column and make sure its set as "date" in formatting
2) if you dont have a date table click on modelling 'New Table' and paste this in:
Date =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 4, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)
3) Link your new date table to your hospital data's duplicate 'date' column in your data (easy this way trust me)
4) Now create a new % measure (say "HAC_CSV_Data[% of HAC's]" and using this create 3 new date range measures:
% of HAC's (MTD) = CALCULATE(HAC_CSV_Data[% of HAC's], DATESMTD('Date'[Date]))
% of HAC's (Prev MTD) = CALCULATE(HAC_CSV_Data[% of HAC's], PREVIOUSMONTH(DATESMTD('Date'[Date])))
% of HAC's (YTD) = TOTALYTD(CALCULATE(HAC_CSV_Data[% of HAC's]),'Date'[Date])
5) once those are created you will need to click on the column tools for each one and format them as %
6) Using the Date table you created and linked to your data - add the Year and Month (only) as a hierarchy into a table visualization, and then add your new 3 measures, just to check they work.

For graphs/charts that flow over years I use the MTD measure the most, not the YTD, as it just works best.

The 3 measures work great in cards btw - flawlessly! (for me) and to do date selections for the end user I add a "Chiclet" into PBi from the free apps and have one for year, and one for month. (or create a new date column = format as dd/mm/yyyy - and to sort, which is a nightmare, create another column formatted as mm/yyyy - and sort using that.) Chiclet works best for me due to the sheer amount of filtering I use in measures, but only if I use the MTD, Prev MTD and YTD cards, otherwise a normal date slicer is fine...

PS - I work with Hospital data all day every day - so this should be easy stuff (wait till you get to title changes and chart x axis changes by selection 🙂

Regards
Andrew