
1. Re: Divide Subtotals and show value in new column
edgarernesto.hernandez Jul 29, 2013 9:02 AM (in response to buildstar)1 of 1 people found this helpfulHi
The best way I have found is to create a dashboard out of the first sheet and create a second sheet with only the calculation of 36585/680. Then, you can use the float option to drag it to where you want to show the result. It's not as convenient as showing it directly on the same sheet but at least it works.
So
1. Create a new sheet and a new calculation:
SUM(IF [test]="Hold" THEN [search] ELSE 0 END)/SUM(IF [test]="Err" THEN [search] ELSE 0 END)
2. Drag the Measure names field on the Rows section.
3.Filter the brand field ton only show 'b'
4. Filter the Measure names to only show 'search'
5. Edit the alias of the line to have it show Total or Ratio, or whatever you want to name that calculation.
6. Create a new dashboard and add the two sheets
7. Make the second sheet float and drag it to place it where you want to display it.
As for your second question, the same solution can be applied to show the exact total (create a calculation on a separate sheet and add it to the dashboard)

2. Re: Divide Subtotals and show value in new column
buildstar Jul 29, 2013 10:46 PM (in response to edgarernesto.hernandez)Hi,
I am getting nulls and zero on calculation.
Can you please help by updating the workbook with your inputs.
Thanks

3. Re: Divide Subtotals and show value in new column
buildstar Jul 31, 2013 7:13 AM (in response to buildstar)Any help on doing such computation. Please update the wb with the possible solution
Thanks

4. Re: Divide Subtotals and show value in new column
buildstar Aug 1, 2013 2:07 AM (in response to buildstar) 
5. Re: Re: Divide Subtotals and show value in new column
Tracy Rodgers Aug 1, 2013 4:49 PM (in response to buildstar)Hi Gallop,
Here is the suggestion that Edgar made. Still trying to come up with another solution.
Tracy

Sample.twbx.zip 25.4 KB


6. Re: Re: Re: Divide Subtotals and show value in new column
buildstar Aug 5, 2013 6:25 AM (in response to Tracy Rodgers)I have been able to use the solution given be edgar.
Please find attached image. Problem is I have 2 dropdowns and if no data is available in either the rows go blank and there is a gap between the rows and total or the rows and calculation overlaps. As suggested the total is in a sheet and is floating on the dashboard. Please suggest how to show the rows with NULL or NA even if data is not available. This way the gap will not be visible.
Thanks

Untitled.png 16.1 KB


7. Re: Re: Re: Divide Subtotals and show value in new column
Patrick A Van Der Hyde Aug 6, 2013 10:06 AM (in response to Tracy Rodgers)Hello
The attached workbook utilizes table calculations to derive the Numerator and Denominator and extend these over the rows of data to achieve the 36585/680 at a per row level.
Denominator:
IF INDEX()=1 THEN WINDOW_SUM(SUM([search]),0,5INDEX() )
Else Previous_Value(1)
END
Numerator:
IF Index()>5
Then
If INDEX() =6 THEN WINDOW_SUM(SUM([search]),0,10INDEX())
Else Previous_Value(1)
END
End
Then simply using these calculated fields in a new calculated field that creates the fraction.
I do not know of a possible way to make the result appear on the Total line however.

sample_new_2.twbx.zip 18.1 KB


8. Re: Divide Subtotals and show value in new column
buildstar Aug 6, 2013 11:38 PM (in response to Patrick A Van Der Hyde)Thanks a lot Patrick...just the perfect solution to the problem.
Can any please help with solution to display the calculated value in the total line ?
Thanks