• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to write formula in calculated field to calculate months of stock/ sales target

P

powerpivot beginner

Member
HI, I have just started to use powerpivot in excel 2013.
In the past, I could just select the field I need to calculate but now with powerpivot, I need to actually type in the formula. It may seem very easy to most of you but I am having problem writing a formula. I hope someone can help me.

As per attachment,

I have stocked a product that is meant for a few customers in my business plan.
I have a sale target for each customer each month. I do not allocate the stock to each customer yet because I do not know how much I will sell to each customer so I created a customer called Stock. But I want to know how many months of stock I have now based on Stock/ total target. What should I do

Thanks
 

Attachments

powerpivot beginner said:
HI, I have just started to use powerpivot in excel 2013.
In the past, I could just select the field I need to calculate but now with powerpivot, I need to actually type in the formula. It may seem very easy to most of you but I am having problem writing a formula. I hope someone can help me.

As per attachment,

I have stocked a product that is meant for a few customers in my business plan.
I have a sale target for each customer each month. I do not allocate the stock to each customer yet because I do not know how much I will sell to each customer so I created a customer called Stock. But I want to know how many months of stock I have now based on Stock/ total target. What should I do

Thanks
Click to expand...

It doesn't appear that you're using PowerPivot in the example you posted - the PowerPivot window is empty. I assume the value in Row 2 of your example is your Total Row? Are you being confused by the #DIV/0! error? If so that is easily rectified by using the following =IFERROR(ROUNDDOWN(E2/B2,0),0) - the rounddown is merely taking away the odd remainder for cleanliness :DD

I'm sure some others will be along shortly with more questions as it's not apparent to me *exactly* what you need, because I'm hopeless at divining such requirements ;)
 
Hi Narayank991 and David.

Thanks for your answers and sorry for not making myself clear. I have revised the example table in the attachment. I created a raw data and created a real pivot table. In the past, I could just use calculated field as per the illustration to obtain a calculated column. The #DIV/0! error does not bother me because I will obtain a number for the no of months of stock/ monthly requirement.

But in new powerpivot, I do not know how to write the DAX formula. Hope someone can help.
 

Attachments

You must log in or register to reply here.
Back
Top