I have a pivot table which I would like users to drill down on rows as follows:
The number they will be viewing is a formula, based on other formulas. The resulting number for this formula seems to change as I drill down. Here is the formula for Total Profit/Loss:
Net Proceeds- ValueHarvested.
Net Proceeds is defined as Sum(GrossProceeds) - Sum(GrossCost)
ValueHarvested is defined as (Sum(Acres) * Avg(CostPerAcre))
The final expression looks like this:
sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)
I've done some digging around in the forums and I can't quite figure out what might be causing the numbers to change as I drill down. Could it be that I need to use Aggr? Perhaps I need to use Dimensionality? Any help would be appreciated. Thanks.
Here's what the numbers look like before I drill down to level 3:
When I drill down to level 3, it changes to this:
Other numbers change too, not just the Total Profit/Loss number that I mentioned above.
I created a separate "test" pivot table, that includes fewer measures. In the test pivot table, everything works fine:
I think it has something to do with the other measures in the "broken" pivot table; either there are too many (11), or one or more of them is causing my expression to get "confused".
Any ideas or suggestions?
Solved! Go to Solution.
Check the expression FldOnBgt%, you are using a key field: "WorkOrder", you should create another field with this, called "WorkOrderCount" to be able to use it in the expression: Sum (FldOnBgtFlag) / Count (DISTINCT WorkOrderCount)
I have had a similar problem.
What I did is generate a table with all the necessary data for the formula and then, taking your example, I used the following formula:
avg(GrossProceeds - GrossCost - (Acres * CostPerAcre))
If this does not work could you upload sample data to be able to test?
PD: sorry, my english is poor. I hope you understand.
You can try this expression too.
Sum(Aggr((sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)),
I think I see what you are doing...but I don't think it is working when I do the same thing in my application. I added the following to the data load editor after the Budget table is built:
Avg(CostPerAcre) as CostPerAcreB
Group By RanchLotWOKey;
I changed my variable to point to the new CostPerAcreB measure, but the numbers are too high.
I've attached an updated copy; if you have time to look I would appreciate it.