Greetings all,
Is there a way to calculate a fixed average price by month? I’m required to factor in a set measure cost for avg unit prices per month.
Right now I can obtain a fixed price for all orders but am unsure on how to do this by month.
Formula for the average sale price per unit:
Avg Sale Price Per Unit = CALCULATE(DIVIDE(SUM(‘Warranty_Costs’[Net_Sales]),[Total Warranty Cores],1),ALLSELECTED(Warranty_Costs))
Any help would be greatly appreciated, thanks!
Nic
@nnouchi
can you upload some sample data and what you’d like to see as an outcome?
Nick,
Here’s a sample of the data. I have my date column tied back to my date table. Ultimately, I would be setting the avg. sale price per unit after the month finished but need to be able to set it by month.
Example of Avg Sale Price per unit.xlsx (16.1 KB)
@nnouchi,
Here’s what I got to start.
Created a Calendar table that had date, month and year and related that to your main table. Be sure to use the columns from the calendar table for any filters (rows, columns, slicers, etc.)
Created the following three measures:
Sum of the Avg Sale Price Per unit column
Total of Avg Sale = sum ( Table1[Avg Sale Price Per Unit] )
Then a countrows measures:
# of Rows = COUNTROWS(Table1)
Then a measure to divide them out:
Tot Avg Sale Divided by Rows = DIVIDE( [Total of Avg Sale], [# of Rows] )
Then you get this table:
Maybe what you had in mind?
Here’s the pbix:
Avg Sale Price.pbix (63.6 KB)
Nick
@Nick_M I should have mentioned but this doesn’t work as the Average Sale Price Per Unit is a measure that is already created within my model. The formula of that measure is:
Avg Sale Price Per Unit = CALCULATE(DIVIDE(SUM(‘Warranty_Costs’[Net_Sales]),[Total Warranty Cores],1),ALLSELECTED(Warranty_Costs))
Here’s the costs and date table that I’m using for the calculation.
Avg price per month.pbix (104.3 KB)
I’m not sure whether to adjust for monthly averages before or after the avg. sum daxmeasure listed above.
@nnouchi,
I think i see what you are saying, but not 100% clear. Could you do a quick mock up of what you would want to see? I have an idea, but want to be sure it’s what you had in mind before I spend too much time on it.
@Nick_M
I actually was able to figure out what I needed to do to make this measure work.
I used my [Avg Sale Price Per Unit] measure and specified to return filters on a new Month/year column I created through my Query Editor.
Avg Price Per Core By Month = CALCULATE([Avg Sale Price Per Unit],ALLEXCEPT(Warranty_Costs,Warranty_Costs[Month/Year]),VALUES(Warranty_Costs[Month/Year]))
Got me to this output of fixed averages
