DIVIDE function

[kineg]

Hi everyone!
Hoping maybe someone here can help me with a little problem I am having with the divide function.

I am working in excel 2013 with a tabular model in powerpivot. Here I have columns for total sales and total results. I am trying to get profit in %, and I have been using Profit%:=DIVIDE([TotalSales];[TotalResults])

When used to create a calculated column I get the right percentage for the drilled down results, but as soon as I am higher up in the hierarchy it starts adding the percentages together and the results become wrong. If I use the function as a explicit measure instead it should turn out okay, but seeing as my numbers have both 0-values and negative values something goes wrong. (The same function works just fine as an explicit measure as long as I only have positive, above 0-values.)

[FONT=arial, sans-serif]Any ideas? [/FONT]

 

[scottsen]

Hrm. So, let's just ignore calculated columns, since undoubtedly you are going to want to slice your data by region or whatever... which isn't going to work with calc columns. Measure is the way to go.

So, I guess... what do you mean by "something goes wrong" ? I suspect there is something you don't like about the number it kicks out, and you may need to adjust one of your underlying measures to do something special with the negatives?

 

[kineg]

Hrm. So, let's just ignore calculated columns, since undoubtedly you are going to want to slice your data by region or whatever... which isn't going to work with calc columns. Measure is the way to go.

So, I guess... what do you mean by "something goes wrong" ? I suspect there is something you don't like about the number it kicks out, and you may need to adjust one of your underlying measures to do something special with the negatives?

Hehe, maybe not the best description indeed. I get an error message: ErrorMessage
The underlying measures are straight from the source, they have not been calculated in the data model.

 

[scottsen]

That error is... just what it says. It can't find the column you are talking about. Either you made a typo, or didn't use the TableName[columnname] style (just used [columnname] instead) ?