Sell-through calculation in power-pivot (DAX or calculated column?)

[redalert787]

Hello, 

I'm new to DAX and power pivot world. I'm using power query and power pivot to build dashboards in excel. I want to calculate the sell-through rate of products for our company. We calculate sell-through as:

Net sales for a period/Original purchases 

I have all the data in my database but I don't know how to get the calculation working. If I simply try to add a new custom column in the power query table and do the equation above, it comes up blank. You can see that we calculate time in ISO format 4-4-5. In other words, the sales are measured in weeks/year (01.2020/ 02.2021 etc etc). 

I'm a little lost and most advice is related to powerBI online and not necessarily to what you can do in excel power pivot. 

Any help would be appreciated, I seem to run into problems whenever I'm doing calculations with Power query/power pivot and I'm not sure what I'm doing wrong. 

Thanks, 

Red

Dashboard and queries: 

https://amersportsonline-my.sharepoint.com/:x:/g/personal/ardi_sardashti_amersports_com/EVTOFliD2ilO...

Database: 

https://amersportsonline-my.sharepoint.com/:x:/g/personal/ardi_sardashti_amersports_com/EV6mFQPw7qZC...

[Anonymous]

@redalert787 

With knowledge of dax, if your calculation is Net sales for a period/Original purchases, and the period is weeks and year format. 

You can create following calculate column:

Column = Calculate(sum[net sales]), allexcept(table,[Weeks/Year]))  /  Calculate[sum[original sales], allexcept(table,[Weeks/Year]))

This is power bi forum and we are less like to using power pivot in excel, so lacks such knowledge, although they are relevent in some extent. I recommend you to go for power pivot forum: 

PowerPivot/DAX - PowerPivot Forum

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.