Hello,
I am currently doing a Northwind project as practice on WEBI.
I want to create a report to work out profit. I’ve created dimensions and measures appropriately to work out quantities, price per unit, mark up value on each quantity of product made etc. this enables me to work out profit made on each individual product as a total profit per product.
When it comes to calculating total profit per salesperson, I have to have product names in to stop the previous calculations (mentioned above) being aggregated before the calculation of profit.
I then created copies of the measures and treated them like dimensions by removing the “sum” command, but this had no effect to my report and the same problem occured.
In my report I purely want to show the profit made by each salesman, regardless of what products they have sold, but I cannot remove this as a column as it collapses the table and pre-aggregates the values (ie quantity and mark up etc.) used to calculate profit, hence a wrong value.
Using the sum function on the table with the products name included works, but as mentioned previous, I do not want to include this in my table as not necessary for my reporting requirements.
If you are dead set on getting this formula to work at the report level then you can try using the ForEach() operator. This operator works the same as having the [Product] dimension included in your report without actually having it in your report. So for instance, your forumla could look something like:
=([Quantity]*[Mark Up Value])-[Price Per Unit] ForEach([Product])
That should be roughly what you are looking for. Also, there are multiple ways to do this in the universe level as well!
Thanks for the suggestion, I had previously experimented with this but didn’t think I had applied the ForEach function to all the relevant variables.
I did this and…unfortunately it still did not work without product name in the table. BO automatically wants to aggregate results and when product name is removed I receive a multi-value error on the variables concerned.
I am realising the solution may lie at the universe level.
Hi.
What BO version are using you?
I dont know if i understand well your issue but, in 3.1 Sp2 (i dont know if in 3.0 its avaliable) exists the smart measures, and maybe works fine with your requirements.
Good luck!
(BOB member since 2009-01-26)
(BOB member since 2006-12-10)