Hi Fellow Power BI Users -
I am trying to do something similar to this video but with Avg of Total Sales (by Work Days).
1st, I calculated the total work days:
Work days = CALCULATE(COUNTROWS(FILTER('Date','Date'[Holidays & WorkDays] = "Work Day")), FILTER('Date','Date'[Date] < TODAY()))
2nd,
Avg Sales per Work day = DIVIDE([Total Sales],[Work days])
3rd - This part is not doing what it supposed to do. It’s not summing “Avg Sales per Work Day” by FY.
Avg daily sales per FY (all) = CALCULATE([Avg Sales per Work day], ALL('Date'[FY]))
4th
Avg Sales % = DIVIDE([Avg Sales per Work day],[Avg daily sales per FY (all)],0)
I am missing something very simple but can’t figure out what. Please help!
Kiran
For this one above (number 2)
Use AVERAGEX to get what you need. I believe it should work properly if this.
In this below video I show you exactly what I mean.
You’ll find the formula at 3:45
Still doesn’t give me the total of Avg. sales. In order for me to get the percent of average sales per FY month, I will need the total of Average sales by FY Year. This calculation gives me the average but not total of each FY year. Is there any way I can sum the averages for each month by FY year?
Here is my formula.
Avg. sales per weekday =
Var
workday = FILTER('Date','Date'[Holidays & WorkDays] = "Work Day" && 'Date'[Date] < TODAY())
RETURN
AVERAGEX(workday, [Total Sales (Work Days)])
Can you show me the context in which this result will be in.
It’s very important to know this everytime.
Ie. Place an image in this post showing the current results in a table. That way it is easier to understand what results you are seeing and the context of the calculation
I don’t think it’s doable. Here is the sample.
Totals for Avg. sales per workday should be $8404879 and Avg daily sales per FY (all) also should show that number.
Total Avg Daily Sales% should show 100%
Try this instead of the above
Avg Sales per FY =
CALCULATE( [Total Sales], ALLEXCEPT( Dates, Dates[FY] ) )
You’ll see below for the results I am getting using this formula
Did you mean to use “Avg Sales per Work Day” instead of “Total Sales”?
Avg Sales per FY =
CALCULATE( [Total Sales], ALLEXCEPT( Dates, Dates[FY] ) )
If I use this formula with Total Sales I get right results but I am looking to get same results with Avg Sales per Work Day calculation.
Yes, I’m just showing you the technique to use. But you would need to sub in the correct measure.
Yes, that what I did. I used the same formula to calculate Avg Sales per Work Day but still the same results.
Can you please send me your file. Thanks
You can upload it to this post.
Hi Sam,
I will not be using this metric anymore but thank you for your time!!
Just curious if I share my file here will it be visibly to all members?
Thanks!
Kiran
Ok, yes this is a public forum so everyone can see it.
I bemused what there is no solution here.
It’s really only now that I’ve realised what you need.
The total you have is the problem. You wanted to make that total the sum of all the averages.
See below for how this is done. This is the pattern anyway. You may have to make some small adjustments
Average Sales =
VAR AvgSales = AVERAGEX( Sales, Sales[Quantity] * RELATED( 'Product'[Current Price] ) )
RETURN
IF( HASONEVALUE( Dates[Month] ),
AvgSales,
SUMX( VALUES( Dates[Month] ), AvgSales ) )
Avg Sales per FY =
CALCULATE( [Average Sales], ALLEXCEPT( Dates, Dates[FY] ) )
% per FY = DIVIDE( [Average Sales], [Avg Sales per FY], 0 )
Here are the correct results. Notice the totals
1 Like
It worked! Thank you for taking a time to help!
I can’t share company’s information. Creating sample file close to reality is a lot of work.
