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
Ritaf
Responsive Resident
Responsive Resident

average of yearly percent

Hello All, 

I'am trying to calculate a Average Percentage of Total Monthly Sales from Total yearly Sales in Last 5  complete Years .

I calculted an monthly sales by formula:

month_year_sales=CALCULATE([totalSales$],ALLEXCEPT(dimDate,dimDate[Month of Year),FILTER(dimDate,[year]<>[this year])) 
For yearly sales with great assist from this forum i used:
complete_year_sales =
Var _this_year = year(TODAY())
return
CALCULATE(TOTALYTD(sum(Sales[totalsales),ENDOFYEAR(dimDate[date]),ABS(year(sales[sale's date])<>_this_year))
For percenatge i used:
Percent_of_yearly_Purchase = CALCULATE([month_year_sales]/[complete_year_sales],ALLSELECTED(dimDate[year]))
)

and for averaged percentage i tried to use 

Percent_of_yearly_Purchase / distinctcount(years)

I see that the average isn't working and the problem begind in total of percentage , how can i fix it?Capture.JPG

 

1 ACCEPTED SOLUTION

@Nathaniel_C 

Take a look at the AVERAGEX function.  It will end up being something like.

Percent of yearly purchase =
AVERAGEX (
    VALUES ( dates[MonthYear] ),
    CALCULATE (
        DIVIDE ( [month_year_sales], [complete_year_sales] ),
        ALLSELECTED ( dimDate[year] )
    )
)

Which will show the correct % on the month but at the total level will show the average.

View solution in original post

15 REPLIES 15
Nathaniel_C
Super User
Super User

Hi @Ritaf ,

Not sure if  I understand what you are trying to do. Look at the total of [completeYearSales]. You may need to insert a column that would give you the sum of those sales. Or use the sum of the [monthYearSales] as that would be your real total,yes? Also the average of the sums is not the sum of the averages. Perhaps you might give us what you would expect the results to be.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Ritaf
Responsive Resident
Responsive Resident

Hi,

Thank you for respending. I need an average percent of every month by all comlete years like on attached picture.capture2.JPG

 

Hi @Ritaf ,

Maybe we can get @jdbuchanan71  to step in here.

Nathaniel





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

Proud to be a Super User!




@Nathaniel_C 

Take a look at the AVERAGEX function.  It will end up being something like.

Percent of yearly purchase =
AVERAGEX (
    VALUES ( dates[MonthYear] ),
    CALCULATE (
        DIVIDE ( [month_year_sales], [complete_year_sales] ),
        ALLSELECTED ( dimDate[year] )
    )
)

Which will show the correct % on the month but at the total level will show the average.

It worked!!! Thank you !!!!Heart

Ritaf
Responsive Resident
Responsive Resident

Hi,

following this solution , how can i always show a "total" for last 5 years without slicer's effect.

I need it to "predict" a a sum of dynamic target. My real case isn't an sales , but the percent of economy from purchasing deals of purchase managers, like on attached table.Capture.JPG

 

 

@Ritaf 
In those yellow cells, what is the expected value?  Show the number and what fields you used to caluclate it.

Something like logiks in attached pic. I think i have to make some summerise table , i just dont know how to do thisCapture.JPG

@Ritaf 

Please share your excel file with the example data.

How can i upload a file ?

You can save it to OneDrive or DropBox and share the link here

Ok , i will build pbix with data which i can share ,and i will do it
Ritaf
Responsive Resident
Responsive Resident

there is a link to pbix and to excel file (just a data  example without measures).

https://drive.google.com/drive/folders/1I9R1wwtLYc1sHTifl4UqO23fKxSlEx0V

 

Hi @Ritaf 

I'm having some trouble figuring out what you are looking for and what you want to show on which lines.

In you example excel file you say Jan - Sep is 60% of the yearly total. 
Do you mean it is 60% of the 5 year average yearly amount? Is that only for completed years so it would be the average of 2014 - 2018?
Also, you include an 18% number in your calc, where is that 18% coming from?  

I think the sample data needs to include all the data you used in your excel calc. The 5 years of amounts that you use to get your average so we can understand what date range your are considering 5 years.

 

Hi, it 's too complicate , so we gave up.

However this case showed me that i have a seruous problem with dealing with contexts/ filters "games" , i wil post my questions clearly as another post.

Thank you very much for your attemps to help.

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.

Top Solution Authors