Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Still new at this...Not sure why the graph is automatically taking the average of my data. Is there a way to chart only the data that is in the table even if calculations are being done behind the scenes. The straight table says one thing the chart is different.
The only expression I have is "% of total cost by BU: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST) "
First I have no year selected. When I chart this it takes the average of the years and displays the activity along the "x" and percentage on the "y." So for activity 1 the average is around 14.72% and Activity 2 is .02%
Activity | Business Unit | Year | % of Total Cost by BU |
Activity 1 | BU1 | 2008 | 14.46% |
Activity 1 | BU1 | 2007 | 14.94% |
Activity 2 | BU1 | 2008 | 0.02% |
Activity 2 | BU1 | 2007 | 0.02% |
Next I select 2007. Table automatically changes to below.
Activity | Business Unit | Year | % of Total Cost by BU |
Activity 1 | BU1 | 2007 | 14.94% |
Activity 2 | BU1 | 2007 | 0.02% |
But when I chart this graph it shows the percentage of Activity 1 to be 8.14%. It is counting the sum of costs for 2008 as zero and than taking the average bringing it down to 8.14%. If I select 2007,I want it to chart just 2007 without taking an average. The straight table shows the data I want but the chart is the average with 2008 as zero.
How can I force it to chart 14.94% or am I doing this completely wrong. Thanks!
Ideally a trend chart(bar/line) should not have more than 2 dimensions.
That is why you might not get the desired.
Thanks Rocky. I included year to help make it easier to explain but even with just two dimensions (activity, business unit) it still has the same problem.
Is there a different way to write the expression? I need to know the percentage of (activity dollars)/(total dollars in the business unit). This needs to the case for all dollars in 2007 and 2008 or by each specific year if the user wants to see it individually. The chart doesn't need to show they year because users will know what year(s) has been selected.
I'm not exactly following what you want, but I believe you need to use TOTAL instead of ALL. ALL ignores selections.
Thanks John.
TOTAL doesn't work because it dispalys the percentage in terms of the selection and I want it to give the percentage regardless of what drill-down or selections are made.
This is an example:
1. Raw data
Activity | BU | Year | Cost |
Activity 1 | BU1 | 2008 | 10 |
Activity 1 | BU1 | 2007 | 30 |
Activity 2 | BU1 | 2008 | 25 |
Activity 2 | BU1 | 2007 | 40 |
Activity 3 | BU1 | 2008 | 10 |
Activity 3 | BU1 | 2007 | 30 |
Activity 1 | BU2 | 2008 | 10 |
Activity 1 | BU2 | 2007 | 20 |
Activity 2 | BU2 | 2008 | 30 |
Activity 2 | BU2 | 2007 | 40 |
Activity 3 | BU2 | 2008 | 80 |
Activity 3 | BU2 | 2007 | 30 |
If the user selects no year the table should show
Activity | BU | Year | Cost | BU Total | % of Total Cost by BU | |
Activity 1 | BU1 | 2007 and 2008 | 40 | 145 | 27.59% | |
Activity 2 | BU1 | 2007 and 2008 | 65 | 145 | 44.83% | |
Activity 3 | BU1 | 2007 and 2008 | 40 | 145 | 27.59% | 100.00% |
Activity 1 | BU2 | 2007 and 2008 | 30 | 210 | 14.29% | |
Activity 2 | BU2 | 2007 and 2008 | 70 | 210 | 33.33% | |
Activity 3 | BU2 | 2007 and 2008 | 110 | 210 | 52.38% | 100.00% |
If the user selects 2007 the table should be
Activity | BU | Year | Cost | BU Total | % of Total Cost by BU | |
Activity 1 | BU1 | 2007 | 30 | 100 | 30.00% | |
Activity 2 | BU1 | 2007 | 40 | 100 | 40.00% | |
Activity 3 | BU1 | 2007 | 30 | 100 | 30.00% | 100.00% |
Activity 1 | BU2 | 2007 | 20 | 90 | 22.22% | |
Activity 2 | BU2 | 2007 | 40 | 90 | 44.44% | |
Activity 3 | BU2 | 2007 | 30 | 90 | 33.33% | 100.00% |
These tables are working with the current expression. % of Total: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST)
However when I create the chart off of 2007 (the above table) it automatically graphs the bottom data because the cost of 2008 is zero but is included in the BU Total.
Activity | BU | Year | Cost | BU Total | % of Total Cost by BU | |
Activity 1 | BU1 | 2007 | 30 | 145 | 20.69% | |
Activity 2 | BU1 | 2007 | 40 | 145 | 27.59% | |
Activity 3 | BU1 | 2007 | 30 | 145 | 20.69% | 68.97% |
Activity 1 | BU2 | 2007 | 20 | 210 | 9.52% | |
Activity 2 | BU2 | 2007 | 40 | 210 | 19.05% | |
Activity 3 | BU2 | 2007 | 30 | 210 | 14.29% | 42.86% |
Appreciate any help. Thanks.
Still confused. You seem to say you want to use ALL because you "want it to give the percentage regardless of what drill-down or selections are made." But then you seem to complain about the bottom chart "because the cost of 2008... is included in the BU Total". It seems you instead want the first two charts, not the third chart. And you can generate the first two charts by using TOTAL instead of ALL. Specifically:
Cost = sum(Cost)
BU Total = sum(TOTAL <BU> Cost)
% of Total Cost by BU = Cost/"BU Total"
See attached. Is that not what you want?