Forum Discussion
Calculated Columns Errors
I'm unable to find this solution posted elsewhere; apologies if this has already been answered
I'm attempting to calculate the "cost per item" up through a certain fiscal week for both 2015 and 2016.
I have 6 columns:
-Fiscal Week
-Item
-2015 Sales Dollars
-2015 Sales Qty
-2016 Sales Dollars
-2016 Sales Qty
I am looking to compare the cost per item for 2016 to 2015 at this point in time, so I have a filter on fiscal week to be less than or equal to 15. I am attempting to have two calculated columns, 2015 $/qty and 2016 $/qty to determine the cost at this point. In excel I do this by using sumifs to sum the $ and qtys for each item for the current number of available weeks.
Does anyone have any advice on how to do this? I am getting the same value for all items, which obviously isn't true.
6 Replies
Do you have a Calendar Table? And can you provide a little sample data?
Thank you for your help!
I pasted some data below. The reason there are two lines per week per item is that there are two different types. I did not include this column as I am just trying to get the overall cost per item at week 3. (so data from weeks 1-3 for both years).
This is very simple in excel...not sure how to do it in power bi! In my master set of data there are more items and obviously more weeks. A calculated column of sum(2015 dollars)/sum(2015 qty) just does it for the entire set of data.
Thank you!
FW Item 2015 Sales Dollars 2015 Sales Quantity (Cs) 2016 Sales Dollars 2016 Sales Quantity (Cs) 1 Apple $421.64 2 $386.38 2 1 Apple $1,684.02 8 $1,023.05 5 1 Banana $1,818.64 16 $1,350.06 12 1 Banana $3,159.63 22 $2,055.91 14 2 Apple $346.09 1 $370.49 2 2 Apple $1,277.96 7 $1,529.64 8 2 Banana $1,573.92 13 $1,522.50 13 2 Banana $2,979.03 20 $2,732.27 18 3 Apple $400.67 2 $411.08 2 3 Apple $1,770.67 9 $1,844.39 9 3 Banana $1,999.06 17 $1,812.70 15 3 Banana $4,307.94 28 $3,204.44 21 4 Apple $532.92 2 4 Apple $2,083.90 11 4 Banana $2,197.09 19 4 Banana $4,122.36 27 also, I do not have a calendar table, but could obviously create one, maybe using a hierarchy?
I've never needed to do that before when doing SQL dumped into Excel so just a bit confused!
I would think a measure in the form of:
2015 Cost Per Item = SUM([2015 Sales Dollars]) / SUM([2015 Sales Qty)
2016 Cost Per Item = SUM([2016 Sales Dollars]) / SUM([2016 Sales Qty])
Create a table for [Fiscal Week], [Item] and your two new measures.
Edit: Oh wait, you would probably need a another measure for a running total/average, look at daxpatterns.com for running total/average patterns. Should be able to use them with your two new measures.
Also if you do have a calendar table several of the time Intel functions can take as a parameter FY end say eg (6/30)
