Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddharthj
Regular Visitor

Average prices based on Daily quote

Friends,

I have a table for daily prices quotes and I want to calculated monthly and weekly avearge of the quotations to apply on the sales qty to calcualte the revenue. Also, quotation values for Sat and Sun to be excluded from the calculations.

I have tried various combinations for AVERAGE and AVERAGEX but looks like I'm missing out some context.

Appreciate if soemone can guide.

Thanks,

Siddharth

 

 

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @siddharthj ,

 

We can use the following steps to meet your requirement.

 

1. Create three columns which are month, weekday and week.

 

Month = MONTH('Table'[date])
week = WEEKNUM('Table'[date],2)
weekday = WEEKDAY('Table'[date],2)

 

average 1.jpg 

 

2. Then we can create a measure to calculate the average of quotation, the weekday is less than 6 can filter the workday.

 

Measure = 
var x = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[weekday]<6))
var y = CALCULATE(COUNT('Table'[date]),FILTER('Table','Table'[weekday]<6))
return
x / y

 

3. At last we can create a matrix table, Month and week in Rows, measure in Values. The result like this,

 

average 2.jpg

 

average 3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-zhenbw-msft ,

I have been using similar logic with 'AVERAGEIFS" in excel to generate view (below table with one month data) where I can see on any particular day how the various average compares and take a decision. I want the generate the same in power bi visuals. 

I can share the pbix or excel. please message me you email.

DateDay NameDayDay NumWeekMonth DAILY QUOTE  AVG. MONTH  AVG.WEEK  AVG. HM PMPWPHM
1-Jan-20Wed1311                    69.88                    65.66               70.21           68.63          -            -            -  
2-Jan-20Thu2411                    69.47                    65.66               70.21           68.63          -            -            -  
3-Jan-20Fri3511                    71.28                    65.66               70.21           68.63          -            -            -  
4-Jan-20Sat4611                    71.28                    65.66               70.21           68.63          -            -            -  
5-Jan-20Sun5711                    71.28                    65.66               70.21           68.63          -            -            -  
6-Jan-20Mon6121                    71.74                    65.66               69.41           68.63          -     70.21          -  
7-Jan-20Tue7221                    69.76                    65.66               69.41           68.63          -     70.21          -  
8-Jan-20Wed8321                    70.66                    65.66               69.41           68.63          -     70.21          -  
9-Jan-20Thu9421                    67.85                    65.66               69.41           68.63          -     70.21          -  
10-Jan-20Fri10521                    67.04                    65.66               69.41           68.63          -     70.21          -  
11-Jan-20Sat11621                    67.04                    65.66               69.41           68.63          -     70.21          -  
12-Jan-20Sun12721                    67.04                    65.66               69.41           68.63          -     70.21          -  
13-Jan-20Mon13131                    66.55                    65.66               65.60           68.63          -     69.41          -  
14-Jan-20Tue14231                    65.19                    65.66               65.60           68.63          -     69.41          -  
15-Jan-20Wed15331                    65.52                    65.66               65.60           68.63          -     69.41          -  
16-Jan-20Thu16431                    65.13                    65.66               65.60           62.93          -     69.41   68.63
17-Jan-20Fri17531                    65.63                    65.66               65.60           62.93          -     69.41   68.63
18-Jan-20Sat18631                    65.63                    65.66               65.60           62.93          -     69.41   68.63
19-Jan-20Sun19731                    65.63                    65.66               65.60           62.93          -     69.41   68.63
20-Jan-20Mon20141                    66.51                    65.66               64.78           62.93          -     65.60   68.63
21-Jan-20Tue21241                    65.42                    65.66               64.78           62.93          -     65.60   68.63
22-Jan-20Wed22341                    64.57                    65.66               64.78           62.93          -     65.60   68.63
23-Jan-20Thu23441                    63.41                    65.66               64.78           62.93          -     65.60   68.63
24-Jan-20Fri24541                    63.99                    65.66               64.78           62.93          -     65.60   68.63
25-Jan-20Sat25641                    63.99                    65.66               64.78           62.93          -     65.60   68.63
26-Jan-20Sun26741                    63.99                    65.66               64.78           62.93          -     65.60   68.63
27-Jan-20Mon27151                    63.99                    65.66               60.10           62.93          -     64.78   68.63
28-Jan-20Tue28251                    59.02                    65.66               60.10           62.93          -     64.78   68.63
29-Jan-20Wed29351                    60.99                    65.66               60.10           62.93          -     64.78   68.63
30-Jan-20Thu30451                    58.25                    65.66               60.10           62.93          -     64.78   68.63
31-Jan-20Fri31551                    58.26                    65.66               60.10           62.93          -     64.78   68.63

 

 

Thanks,

Siddharth

amitchandak
Super User
Super User

For monthly data you can use , for Weekday you can have workday flag

MTD Sales = CALCULATE(Average(Sales[Sales price]),DATESMTD('Date'[Date]),'Date'[Workday]=1)
last MTD Sales = CALCULATE(Average(Sales[Sales price]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),'Date'[Workday]=1)

 

For Week refer How we can use week rank in file

Current Week =CALCULATE(Average(Sales[Sales price]),Filter(All('Date'),'Date'[Week Rank] = MAx('Date'[Week Rank])),'Date'[Workday]=1)

 

Refer tgis file for Week : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Hi @amitchandak ,

the formaula works and genertes the averages, but when visualising in the table date wise the average values do not hold and displays daily price. for example , see the table below, 01-Jan Average shown is daily price 77 instead of Jan monthly Average of 72.0. When listing datewise the context of the calculation changes from Monthly to Daily.

MonthAverage DateAverage
1        72.00 1-Jan-20        77.00
2        61.00 2-Jan-20        77.00
3        36.00 3-Jan-20        79.00
4        20.00 4-Jan-20        79.00
   5-Jan-20        79.00
   6-Jan-20        79.00
   7-Jan-20        77.00

 

Thanks,

Siddharth

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.