topic Re: average of yearly percent in DAX Commands and Tips

average of yearly percent

Ritaf — Mon, 21 Oct 2019 11:27:34 GMT

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?

Re: average of yearly percent

Nathaniel_C — Mon, 21 Oct 2019 13:21:54 GMT

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

Re: average of yearly percent

Ritaf — Mon, 21 Oct 2019 14:15:25 GMT

Hi,

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

Re: average of yearly percent

Nathaniel_C — Mon, 21 Oct 2019 17:45:53 GMT

Hi Ritaf ,

Maybe we can get jdbuchanan71 to step in here.

Nathaniel

Re: average of yearly percent

jdbuchanan71 — Mon, 21 Oct 2019 18:39:30 GMT

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.

Re: average of yearly percent

Ritaf — Tue, 22 Oct 2019 05:49:02 GMT

It worked!!! Thank you !!!!:heart:

Re: average of yearly percent

Ritaf — Tue, 22 Oct 2019 08:00:52 GMT

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.

Re: average of yearly percent

jdbuchanan71 — Tue, 22 Oct 2019 13:13:04 GMT

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

Re: average of yearly percent

Ritaf — Wed, 23 Oct 2019 10:13:52 GMT

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

Re: average of yearly percent

jdbuchanan71 — Wed, 23 Oct 2019 13:04:53 GMT

Ritaf

Please share your excel file with the example data.

Re: average of yearly percent

Ritaf — Wed, 23 Oct 2019 13:09:22 GMT

How can i upload a file ?

Re: average of yearly percent

jdbuchanan71 — Wed, 23 Oct 2019 13:10:07 GMT

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

Re: average of yearly percent

Ritaf — Wed, 23 Oct 2019 13:14:44 GMT

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

Re: average of yearly percent

Ritaf — Thu, 24 Oct 2019 07:43:38 GMT

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

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

Re: average of yearly percent

jdbuchanan71 — Mon, 28 Oct 2019 13:39:48 GMT

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.

Re: average of yearly percent

Ritaf — Wed, 30 Oct 2019 07:36:22 GMT

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.