Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Help calculating daily average

Daily Average Sales= 
AVERAGEX(KEEPFILTERS(VALUES('LNApps_AppDateDim'[ActualDate])),CALCULATE(SUM('LNApps_Facts'[Total_Sales])))

I am trying to get the daily average sales, but the numbers the formula is giving me above is not giving me the right numbers. Is there any way to have this formula find the amount of days in any month and divide the total sales by that number? Also with that, is there a way to not count weekends in a month? 

 

 

 

http://community.powerbi.com/t5/Desktop/Average-Daily-Spend-by-Month/m-p/179014#M78344 This thread seems to have this formula that is close to what I am after. 

average = Var currentmonth=MAX(Table[Month])
RETURN 
IF(
	FIRSTNONBLANK(MAX(Table[Month]),1)=currentmonth,
	Table[Average_Current],
	CALCULATE(AVERAGE(Table[price]),FILTER(Table,Table[Month]))
	
)

Thanks in advance

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Still looking for help on this

    • Thyago_Rezende's avatar
      Thyago_Rezende
      Resolver I
      8 years ago

      Hi,

       

      Try to create expression table using SUMMARIZE function within AVERAGEX (in first parameter) that contain the granularity by months.

       

      Example: if your facto_table shows sales day-by-day, so you should create new table (within averagex) with SUMMARIZE function that contain sales agregated by months only.

       

      Regards,

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi Anonymous,

     

    You can create a calculated column to return weekday, then create a measure use AVERAGEX() function: 

     

    WeekDay = WEEKDAY('Table1'[Date],1)

     

    DailyAverageExceptWeekends = AVERAGEX(FILTER(ALL('Table1'),'Table1'[WeekDay] <> 7 && 'Table1'[WeekDay]<>1 && MONTH('Table1'[Date])=MONTH(MAX('Table1'[Date]))),'Table1'[Sales])

     

     

    For more information, you can download attached pbix file for a look. 

     

    Best Regards,
    Qiuyun Yu