Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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)
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,
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.
Date | Day Name | Day | Day Num | Week | Month | DAILY QUOTE | AVG. MONTH | AVG.WEEK | AVG. HM | PM | PW | PHM |
1-Jan-20 | Wed | 1 | 3 | 1 | 1 | 69.88 | 65.66 | 70.21 | 68.63 | - | - | - |
2-Jan-20 | Thu | 2 | 4 | 1 | 1 | 69.47 | 65.66 | 70.21 | 68.63 | - | - | - |
3-Jan-20 | Fri | 3 | 5 | 1 | 1 | 71.28 | 65.66 | 70.21 | 68.63 | - | - | - |
4-Jan-20 | Sat | 4 | 6 | 1 | 1 | 71.28 | 65.66 | 70.21 | 68.63 | - | - | - |
5-Jan-20 | Sun | 5 | 7 | 1 | 1 | 71.28 | 65.66 | 70.21 | 68.63 | - | - | - |
6-Jan-20 | Mon | 6 | 1 | 2 | 1 | 71.74 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
7-Jan-20 | Tue | 7 | 2 | 2 | 1 | 69.76 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
8-Jan-20 | Wed | 8 | 3 | 2 | 1 | 70.66 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
9-Jan-20 | Thu | 9 | 4 | 2 | 1 | 67.85 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
10-Jan-20 | Fri | 10 | 5 | 2 | 1 | 67.04 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
11-Jan-20 | Sat | 11 | 6 | 2 | 1 | 67.04 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
12-Jan-20 | Sun | 12 | 7 | 2 | 1 | 67.04 | 65.66 | 69.41 | 68.63 | - | 70.21 | - |
13-Jan-20 | Mon | 13 | 1 | 3 | 1 | 66.55 | 65.66 | 65.60 | 68.63 | - | 69.41 | - |
14-Jan-20 | Tue | 14 | 2 | 3 | 1 | 65.19 | 65.66 | 65.60 | 68.63 | - | 69.41 | - |
15-Jan-20 | Wed | 15 | 3 | 3 | 1 | 65.52 | 65.66 | 65.60 | 68.63 | - | 69.41 | - |
16-Jan-20 | Thu | 16 | 4 | 3 | 1 | 65.13 | 65.66 | 65.60 | 62.93 | - | 69.41 | 68.63 |
17-Jan-20 | Fri | 17 | 5 | 3 | 1 | 65.63 | 65.66 | 65.60 | 62.93 | - | 69.41 | 68.63 |
18-Jan-20 | Sat | 18 | 6 | 3 | 1 | 65.63 | 65.66 | 65.60 | 62.93 | - | 69.41 | 68.63 |
19-Jan-20 | Sun | 19 | 7 | 3 | 1 | 65.63 | 65.66 | 65.60 | 62.93 | - | 69.41 | 68.63 |
20-Jan-20 | Mon | 20 | 1 | 4 | 1 | 66.51 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
21-Jan-20 | Tue | 21 | 2 | 4 | 1 | 65.42 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
22-Jan-20 | Wed | 22 | 3 | 4 | 1 | 64.57 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
23-Jan-20 | Thu | 23 | 4 | 4 | 1 | 63.41 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
24-Jan-20 | Fri | 24 | 5 | 4 | 1 | 63.99 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
25-Jan-20 | Sat | 25 | 6 | 4 | 1 | 63.99 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
26-Jan-20 | Sun | 26 | 7 | 4 | 1 | 63.99 | 65.66 | 64.78 | 62.93 | - | 65.60 | 68.63 |
27-Jan-20 | Mon | 27 | 1 | 5 | 1 | 63.99 | 65.66 | 60.10 | 62.93 | - | 64.78 | 68.63 |
28-Jan-20 | Tue | 28 | 2 | 5 | 1 | 59.02 | 65.66 | 60.10 | 62.93 | - | 64.78 | 68.63 |
29-Jan-20 | Wed | 29 | 3 | 5 | 1 | 60.99 | 65.66 | 60.10 | 62.93 | - | 64.78 | 68.63 |
30-Jan-20 | Thu | 30 | 4 | 5 | 1 | 58.25 | 65.66 | 60.10 | 62.93 | - | 64.78 | 68.63 |
31-Jan-20 | Fri | 31 | 5 | 5 | 1 | 58.26 | 65.66 | 60.10 | 62.93 | - | 64.78 | 68.63 |
Thanks,
Siddharth
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.
Month | Average | Date | Average | |
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |