Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Sample Data :
Date | Actauls |
Jan-21 | 80 |
Feb-21 | 70 |
Mar-21 | 80 |
Apr-21 | 90 |
May-21 | 113 |
Jun-21 | 110 |
Jul-21 | 100 |
Aug-21 | 88 |
Sep-21 | 85 |
Oct-21 | 77 |
Nov-21 | 75 |
Dec-21 | 82 |
Jan-22 | 85 |
Feb-22 | 85 |
Mar-22 | 93 |
Apr-22 | 95 |
May-22 | 125 |
Jun-22 | 115 |
Jul-22 | 102 |
Aug-22 | 102 |
Sep-22 | 90 |
Oct-22 | 78 |
Nov-22 | 72 |
Dec-22 | 78 |
Jan-23 | 105 |
Feb-23 | 85 |
Mar-23 | 82 |
Apr-23 | 115 |
May-23 | 131 |
Jun-23 | 120 |
Jul-23 | 113 |
Aug-23 | 110 |
Sep-23 | 95 |
Oct-23 | 85 |
Nov-23 | 83 |
Dec-23 | 80 |
calculation
1) Divide the data based on the number of Seasons
12 Seasons - Jan to Dec in a particular year . I trying find the Find the Demand of each season across the Time Series
Like below
Month | 2021 | 2022 | 2023 |
Jan | 80 | 85 | 105 |
Feb | 70 | 85 | 85 |
Mar | 80 | 93 | 82 |
Apr | 90 | 95 | 115 |
May | 113 | 125 | 131 |
Jun | 110 | 115 | 120 |
Jul | 100 | 102 | 113 |
Aug | 88 | 102 | 110 |
Sep | 85 | 90 | 95 |
Oct | 77 | 78 | 85 |
Nov | 75 | 72 | 83 |
Dec | 82 | 78 | 80 |
I am trying to find the average based on month in all years
Example jan 2021 and jan 2022 & jan 2023 values are following 80,85,105 average is for jan 2021 to 2023 its 90
Month | 2021 | 2022 | 2023 | Average (2021 -23) | |||
Jan | 80 | 85 | 105 | 90 | |||
Feb | 70 | 85 | 85 | 80 | |||
Mar | 80 | 93 | 82 | 85 | |||
Apr | 90 | 95 | 115 | 100 | |||
May | 113 | 125 | 131 | 123 | |||
Jun | 110 | 115 | 120 | 115 | |||
Jul | 100 | 102 | 113 | 105 | |||
Aug | 88 | 102 | 110 | 100 | |||
Sep | 85 | 90 | 95 | 90 | |||
Oct | 77 | 78 | 85 | 80 | |||
Nov | 75 | 72 | 83 | 77 | |||
Dec | 82 | 78 | 80 | 80 |
finally Calculate Overall Average Demand for all seasons (Months)
Month | 2021 | 2022 | 2023 | Average (2021 -23) | Overall Average of Seasons | ||
Jan | 80 | 85 | 105 | 90 | 94 | ||
Feb | 70 | 85 | 85 | 80 | 94 | ||
Mar | 80 | 93 | 82 | 85 | 94 | ||
Apr | 90 | 95 | 115 | 100 | 94 | ||
May | 113 | 125 | 131 | 123 | 94 | ||
Jun | 110 | 115 | 120 | 115 | 94 | ||
Jul | 100 | 102 | 113 | 105 | 94 | ||
Aug | 88 | 102 | 110 | 100 | 94 | ||
Sep | 85 | 90 | 95 | 90 | 94 | ||
Oct | 77 | 78 | 85 | 80 | 94 | ||
Nov | 75 | 72 | 83 | 77 | 94 | ||
Dec | 82 | 78 | 80 | 80 | 94 | ||
Avg | 88 | 93 | 100 | 94 |
How to achive this ? please help me i want it existing table itself dont want new table .
Solved! Go to Solution.
See the attached pbix.
I changed the previous year average measure to only include 2021-2023. I added a 2024 measure and created a new matrix visual with the values switched to rows.
Proud to be a Super User! | |
Hi @THENNA_41
Please allow me to provide another insight:
Here's the Table for test:
Table:
Then add a measure:
MEASURE =
IF (
HASONEVALUE ( 'Table'[Date] ),
SUM ( 'Table'[Actauls] ),
AVERAGE ( 'Table'[Actauls] )
)
Finally create a matrix, the result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jgeddes Thank you so much for your help.. also i want show 2024 seperatly like screen shot
Previous Data its 2021 - 2023. so i want create like above for 2024?
See the attached pbix.
I changed the previous year average measure to only include 2021-2023. I added a 2024 measure and created a new matrix visual with the values switched to rows.
Proud to be a Super User! | |
@jgeddes Thank you so much for your support ... now applied above measure in report
One approach is to create an individual measure for each situation. In this case there will be 5 measures.
Average 2021 - 2023 =
AVERAGEX(
'Table',
'Table'[Actauls]
)
Average 2021 =
AVERAGEX(
FILTER('Table', 'Table'[Date].[Year] = 2021),
'Table'[Actauls]
)
Change the year value in the above for the remaining two years.
Average of All Seasons =
var _vTable =
SUMMARIZE(
'Table',
'Table'[Date].[Month],
"__value", CALCULATE(AVERAGE('Table'[Actauls]), ALL('Table'))
)
Return
AVERAGEX(_vTable, [__value])
Hope this gets you pointed in the right direction.
Proud to be a Super User! | |
Thank you so much for your support
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |