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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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?

1 ACCEPTED SOLUTION
Super User

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.

15 REPLIES 15
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!

Responsive Resident

Hi,

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

Super User

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!

Super User

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.

Responsive Resident

It worked!!! Thank you !!!!

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.

Super User

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

Responsive Resident

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

Super User

Please share your excel file with the example data.

Responsive Resident
How can i upload a file ?
Super User

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

Responsive Resident
Ok , i will build pbix with data which i can share ,and i will do it
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

Super User

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.

Responsive Resident

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors