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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
THENNA_41
Post Partisan
Post Partisan

Find the Average Demand of each season across the Time Series

 

Sample Data : 

 

DateActauls
Jan-2180
Feb-2170
Mar-2180
Apr-2190
May-21113
Jun-21110
Jul-21100
Aug-2188
Sep-2185
Oct-2177
Nov-2175
Dec-2182
Jan-2285
Feb-2285
Mar-2293
Apr-2295
May-22125
Jun-22115
Jul-22102
Aug-22102
Sep-2290
Oct-2278
Nov-2272
Dec-2278
Jan-23105
Feb-2385
Mar-2382
Apr-23115
May-23131
Jun-23120
Jul-23113
Aug-23110
Sep-2395
Oct-2385
Nov-2383
Dec-2380

 

 

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  

 

Month202120222023
    
Jan8085105
Feb708585
Mar809382
Apr9095115
May113125131
Jun110115120
Jul100102113
Aug88102110
Sep859095
Oct777885
Nov757283
Dec827880

 

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

 Month202120222023 Average (2021 -23)
        
 Jan8085105 90 
 Feb708585 80 
 Mar809382 85 
 Apr9095115 100 
 May113125131 123 
 Jun110115120 115 
 Jul100102113 105 
 Aug88102110 100 
 Sep859095 90 
 Oct777885 80 
 Nov757283 77 
 Dec827880 80 

 

finally  Calculate Overall Average Demand for all seasons (Months) 

 

        
Month202120222023 Average (2021 -23)Overall Average
of Seasons
        
Jan8085105 90 94
Feb708585 80 94
Mar809382 85 94
Apr9095115 100 94
May113125131 123 94
Jun110115120 115 94
Jul100102113 105 94
Aug88102110 100 94
Sep859095 90 94
Oct777885 80 94
Nov757283 77 94
Dec827880 80 94
        
Avg8893100 94  

 

How to achive this ? please help me  i want it existing table itself dont want new table .

1 ACCEPTED 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.

jgeddes_0-1727289721520.pngjgeddes_1-1727289734402.png

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

6 REPLIES 6
v-zhengdxu-msft
Community Support
Community Support

Hi @THENNA_41 

 

Please allow me to provide another insight:

Here's the Table for test:

Table:

vzhengdxumsft_0-1727318436727.png

Then add a measure:

 

 

 

MEASURE =
IF (
    HASONEVALUE ( 'Table'[Date] ),
    SUM ( 'Table'[Actauls] ),
    AVERAGE ( 'Table'[Actauls] )
)

 

 

 

Finally create a matrix, the result is as follow:

vzhengdxumsft_2-1727318752260.png

 

 

 

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.

THENNA_41
Post Partisan
Post Partisan

@jgeddes  Thank you so much for your help.. also i want show 2024 seperatly  like screen shot                                  

THENNA_41_0-1727288766179.png

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.

jgeddes_0-1727289721520.pngjgeddes_1-1727289734402.png

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@jgeddes  Thank you so much for your support ... now  applied above measure in report 

jgeddes
Super User
Super User

One approach is to create an individual measure for each situation. In this case there will be 5 measures.

jgeddes_0-1727275050061.png

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you so much for your support 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors