Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Still looking for help on this
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,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |