Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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?
Solved! Go to Solution.
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.
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
Proud to be a Super User!
Hi,
Thank you for respending. I need an average percent of every month by all comlete years like on attached picture.
Hi @Ritaf ,
Maybe we can get @jdbuchanan71 to step in here.
Nathaniel
Proud to be a 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.
It worked!!! Thank you !!!!
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.
@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 this
You can save it to OneDrive or DropBox and share the link here
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |